Find name and return header

I'm not sure what the purpose is of the red entry, as it seems to have no effect on the result. In H2 you can have this formula:

=IF(E2="","",IF(ISNUMBER(MATCH(E2,B$2:B$5,0)),1,IF(ISNUMBER(MATCH(E2,C$2:C$5,0)),2,"")))

Hope this helps.

Pete

Why you have "Car" but with "B" not "A", still don't get it, another try with array formula:

=INDEX(B1:C1,MATCH(E2,INDEX(B2:C5,MATCH(F2,A2:A5,0),)))

in condition you have "Car" and "A" (need in the same row)

Thank you for your help!

You´re right Pete the first column is inneccesary. Since all the values in green are different, I could just omit the red values to get the heading result. One question though, would this last formula be valid for such scenario?

=INDEX(B1:C1,MATCH(E2,INDEX(B2:C5,MATCH(F2,A2:A5,0),)))

Your help is very appreciated.

If you just looking header (1 or 2) with condition "Car", change formula to:

=INDEX(B1:C1,MAX(IF(B2:C5=E2,COLUMN(B1:C1)-COLUMN(B1)+1)))

still array formula (means press CTRL+SHIFT+ENTER button together)

That works great Azumi!

Not to be a pain over this again thread, but I just wanted to make a final question again.

How could I get the column heading if I have multiple columns (from 1 to 100) as shown below?

New Picture (1).jpg

I understand this is becoming kind of annoying for you guys. Sorry and thank you for your patience and understanding.