Returning Column Header based on Row and Value

[Chad DiGregorio]

Hi All,

I've run into a problem trying to return a value from the header row
based on locating a row value and a subsequent value within that row
to define the column header needed.

For example in the confusing example below imagine that the top row "
A B C D" is the column headers and the column 1 "A B C D" is the row
labels i've assigned. Given values of "C" and "D" I'd like to write a
formula that searches down column 1 for "C" and then looks across the
row for value "D" and returns the value of the column header "B".
Sorry if this question is worded too obscurely. Thanks for any help.


A B C D
A A B C D
B B C D E
C C D E F
D D E F G

 

[Don Guillett]

One way
=INDEX(1:1,1,MATCH("d",INDIRECT(MATCH("c",A:A)&":"&MATCH("c",A:A))))

 

[Max]

Assuming the table as posted is within A1:E5
and G1:H1 contains the variables: C, D
you could place this in I1:
=INDEX(B1:E1,MATCH(H1,OFFSET(B1:E1,MATCH(G1,A:A,0)-1,,,4),0))
to retrieve the required col header within B1:E1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[T. Valko]

Here's a non-volatile approach:

Column headers in the range B1:E1
Row headers in the range A2:A5
Data in the range B2:E5

A10 = C
B10 = D

=INDEX(B1:E1,MATCH(B10,INDEX(B2:E5,MATCH(A10,A2:A5,0),0),0))