Lookup item in table and get column heading

[jayanderson78]

I am having trouble with this look up. This sounds like an easy thing to do, but for some reason I cannot get this to work...

Here is what I need...

I have two worksheets. The frist one is the reference sheet. it looks something like:

A B C
1 Northeast South West
2 PA TX CA
3 MA OK OR
4 NH LA WA

The second spreadsheet has a number of columns. In column Q is a list of states (PA, MA, OK, etc.). In Column R I want to return the value of the column header (of the first spreadsheet) of the state from column Q. For example, if the state in Q1 is PA I want Northeast in R1. If the state in Q2 is TX then I want South in R2.

Any help would be appreicated.


Jason

[NBVC]

Assuming your original database is in Sheet1!A1:C4 and your list is in Sheet2 starting at Q2, then in R2:

=INDEX(Sheet1!$A$1:$C$1,SUMPRODUCT((Sheet1!$A$2:$C$4=Q2)*COLUMN(Sheet1!$A$2:$C$4)-COLUMN(Sheet1!$A$2)+1))

copied down