If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Returning Column Header based on Row and Value
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 |
#2
|
|||
|
|||
Returning Column Header based on Row and Value
One way
=INDEX(1:1,1,MATCH("d",INDIRECT(MATCH("c",A:A)&":" &MATCH("c",A:A)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Chad DiGregorio" wrote in message ... 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 |
#3
|
|||
|
|||
Returning Column Header based on Row and Value
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 --- "Chad DiGregorio" wrote in message ... 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 |
#4
|
|||
|
|||
Returning Column Header based on Row and Value
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)) -- Biff Microsoft Excel MVP "Chad DiGregorio" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|