0

Say I have a table:

image description

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.

2 Answers 2

3

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.

enter image description here

1
  • 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
    – Ooker
    Jan 26, 2017 at 16:36
1

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.