Search for a cell in a table and then display the column title in excel
Arielle asks: I have to search for a cell in a table and then display the column title.
search value in cell e1: AA
table in cells A1:C6
A1:x B1:y C1:z
A2:BB B2:CC C2:DD
A3:AA B3:GG C3:AA
A4:CC B4:BLANK C4:EE
A5:FF B5:BLANK C5:HH
A6:BLANK B6:BLANK C6:II
then the values to be displayed from the search would be: x in one cell and z in the next cell.
the display of the search values can either be in a row-(g1: x h1: z) or a column-(g1: x g2: z) Let me know if this type of search is possible, thanks!
Answer:
Array formula in E3:
Copy cell E3 and paste it down as far as needed.
Download excel template
Search for a cell in a table and then display the column title.xls
(Excel 97- 2003 Workbook *.xls)
Functions in this article:
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SMALL(array,k) returns the k-th smallest row number in this data set.
ROW(reference) returns the rownumber of a reference
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
COLUMN(reference) returns the column number of a reference
Related posts:
Search for a cell value in an excel table
Search and display all cells that contain all search strings in excel
Lookup with multiple criteria and display multiple search results using excel formula, part 4
Lookup with multiple criteria and display multiple search results using excel formula
Recommended categories
This works great! the only issue I am having is on my end. I realized that for some reason my table has a space in front of some of the words so cells A2:C6 might have a space before the double letter. There is no way that I can get around this because I am pasting this table from somewhere else and the table is too large to delete all the spaces. Is there a way to make the formula ignore the space before the words? Thanks!
ex:
A2:BB C2:DD
A3: AA C3:AA
A4: CC
see how A3 and C3 are slightly different because A3: has a space before AA and that causes the formula to only then display C3's data when the search says "AA" and not " AA"
@Arielle,
Just TRIM the range...
=INDEX($A$1:$C$1, SMALL(IF(COUNTIF($E$1, TRIM($A$2:$C$6))>0, COLUMN($A$2:$C$6)), ROW(A1)))
Hi,
I am unable to ask a separate question, however I think it relates to this so will post it here if anyone can help me.
I am trying to set up a spreadsheet for children's reading levels and the dates they achieve each level. The problem I am having is I want a quick view to the current level they are on. My spreadsheet is set up as:
A1: Name B1:Current Level C1: level 1 D1: Level 2 E1: Level 3 etc up to level 10
A2: Child a B2: C2:01/09/11 D2: 3/10/11 E2: 2/11/11
A3: child b B3: C3:01/09/11 D3: 6/11/11
I am able to use =max(c2:P2) to find the newest date and put this into B2, however I would like it to display the column title (the actual level). (e.g. for child a it would display 'Level 3' in B2)
I hope you can understand what I am asking.
thanks for your help
magicstar29,