Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column data retrieved based on value in header row | Excel Discussion (Misc queries) | |||
pick from table based on row and column header | Excel Worksheet Functions | |||
Copy data from other worksheet based on column header | Excel Discussion (Misc queries) | |||
Returning Column Header | Excel Worksheet Functions | |||
Returning column header as result | Excel Worksheet Functions |