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!
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:
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
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Counts the number of cells within a range that meet the given condition
COLUMN(reference) returns the column number of a reference