# How to return the column head if a cell is matched?

Say I have a table: I want to search for a cell, and return its column header. For example, if I search for `Orange`, the result is `Fruit`. I can achieve this by using the nested if (`E2` is `Orange` in here):

``````=IF(NOT(ISERROR(MATCH(E2,A1:A4,0))),A1,IF(NOT(ISERROR(MATCH(E2,B1:B3,0))),B1,C1))
``````

But in a large table this is impossible. Plus that I cannot know if error happens. Is there a better way to get the result? I don't know how many rows each column has. There is no duplicated data in the table. The table does not start in `A1`, and can be moved to another spreadsheet in the future.

If Aggregate is not between the functions you can use:
`=OFFSET(A1,0,SUMPRODUCT(--(A2:C4=E2)*COLUMN(A2:C4))-1)`
Sumproduct will determine the column number for the matching value you are searching for.
Offset will return the Header of the equivalent column. • I find that changing the formula to `=OFFSET(A1,0,SUMPRODUCT(--(A2:C4=E2)*COLUMN(A2:C4))-COLUMN(A2:C4))` will allow us to move the table around, but I'm not sure why Jan 26, 2017 at 16:36

Use AGGREGATE() to return the column number to an INDEX() function:

``````=INDEX(1:1,AGGREGATE(15,6,COLUMN(\$A\$2:\$C\$4)/(\$A\$2:\$C\$4=E2),1))
`````` 