## Search for a cell in a table and then display the column title in excel

*Article updated on July 19, 2017*

**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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 4 Responses to “Search for a cell in a table and then display the column title in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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,