Google-sheets – Search a range for a value, return the value of the first cell in the matched column

formulasgoogle sheets

I have a table, such as:

     A          B         C
1    Animals    Colors    States
2    Cat        Red       CT
3    Dog        Orange    CA
4    Mouse      Yellow    CO

I want to search for Yellow, and get Colors as a result.

I feel like one of the LOOKUP options should work here, but I'm not having luck. Is there a way to do this with a formula?

D1: Yellow

E1:

=ARRAYFORMULA(TEXTJOIN(,1,IF(D1=A2:C4,A1:C1,)))

=index(A:A;max(arrayformula(row(B:B)*(B:B="X"))))

filter works, but with query you can get all needed columns with one command:

=query(Notes!A2:E, "select B, D, E where A = '"&B1&"'")

The messy end of query string is arranged so that if B1 contains Student Name, the query becomes "select B, D, E where A = 'Student Name'"