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 blog 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
- Search and display a range of values in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
August 17th, 2010 at 4:30 pm
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"
August 17th, 2010 at 6:14 pm
@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)))
December 27th, 2011 at 7:30 pm
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
January 6th, 2012 at 1:22 pm
magicstar29,