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:

=INDEX($A$1:$C$1, SMALL(IF(COUNTIF($E$1, $A$2:$C$6)>0, COLUMN($A$2:$C$6)), ROW(A1))) + CTRL + SHIFT+ ENTER.

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