Lookup values based on column headers?

I have a data validation dropdown list box in "sheet1" cell $J$1. Depending
on what I have selected in that dropdown list I would like it to match or
lookup the data with the respective columns the heading is over in "sheet2".
The receiving array is "Sheet1" Y3:AA2284 and the sending array is "Sheet2"
CF3:HE2284

Also, the column headers are merged for two or three columns each. I am
hoping there might be a way to pull the data from say all columns that the
header is over?

So to clarify, If I select the word "chair" from the dropdown, and "chair"
is over 2 columns CF & CG, then it will show all of those values. Likewise if
there were 3 columns the heading was over it would return the values for all
three columns.

If this requires very much work for you, please know that I very much
appreciate it, as this will really help me out.
--
Thank you!

Assuming your headings are in Sheet2!CF2:HE2, follow this:

in Sheet2:HF2 put "x", this is required as a "back-stop".

then in Sheet1Y3, array entered*:

=IF((COLUMN()-COLUMN($Y$3)+1)MIN(IF(OFFSET(
Sheet2!$CF$2:$HF$2,,MATCH($J$1,Sheet2!$CF$2:$HF$2,
0))"",COLUMN(Sheet2!$CF$2:$HF$2)-CELL("COL",
Sheet2!$CF$2:$HF$2)+1)),"",INDEX(Sheet2!$CF$3:$HE$ 22,
ROW()-ROW($Y$3)+1,MATCH($J$1,Sheet2!$CF$2:$HF$2,0)+
COLUMN()-COLUMN($Y$3)))

Copy Y3 down as far as Y22, then copy Y3:Y22 across as far as required.

HTH
Steve D.

*press Ctrl+Shift+Enter instead of just Enter.



"Doug" wrote in message
...
I have a data validation dropdown list box in "sheet1" cell $J$1. Depending
on what I have selected in that dropdown list I would like it to match or
lookup the data with the respective columns the heading is over in
"sheet2".
The receiving array is "Sheet1" Y3:AA2284 and the sending array is
"Sheet2"
CF3:HE2284

Also, the column headers are merged for two or three columns each. I am
hoping there might be a way to pull the data from say all columns that the
header is over?

So to clarify, If I select the word "chair" from the dropdown, and "chair"
is over 2 columns CF & CG, then it will show all of those values. Likewise
if
there were 3 columns the heading was over it would return the values for
all
three columns.

If this requires very much work for you, please know that I very much
appreciate it, as this will really help me out.
--
Thank you!

I have given this a try and have not been able to get it to work. Can you
help me determine what it wrong with my entries?

Note: I moved some things around prior to your reply but think I changed
them properly. Please double check me?

The receiving array was "Sheet1" Y3:AA2284 and is now "Economy" JO3:JQ2284
The sending array was "Sheet2" CF3:HE2284 and is now "Data" EN3:JM2284
The Validation list box cell is "Economy" $J$1
Also, I originally said that the headers were over either 2 or 3 columns
each, but will this work for a single as well? I just noticed today that I
have several that are not merged single header columns? Thank you very much
for your efforts!


=IF((COLUMN()-COLUMN($JO$3)+1)MIN(IF(OFFSET($EN$2:$JN$2,,MATCH( Economy!$J$1,$EN$2:$JN$2,0))"",COLUMN($EN$2:$JN$ 2)-CELL("COL"$EN$2:$JN$2)+1)),"",INDEX($EN$3:$JM$22,R OW()-ROW($JO$3)+1,MATCH(Economy!$J$1,$EN$2:$JN$2,0)+COL UMN()-COLUMN($JO$3)))
--
Thank you!


"Steve Dunn" wrote:

Assuming your headings are in Sheet2!CF2:HE2, follow this:

in Sheet2:HF2 put "x", this is required as a "back-stop".

then in Sheet1Y3, array entered*:

=IF((COLUMN()-COLUMN($Y$3)+1)MIN(IF(OFFSET(
Sheet2!$CF$2:$HF$2,,MATCH($J$1,Sheet2!$CF$2:$HF$2,
0))"",COLUMN(Sheet2!$CF$2:$HF$2)-CELL("COL",
Sheet2!$CF$2:$HF$2)+1)),"",INDEX(Sheet2!$CF$3:$HE$ 22,
ROW()-ROW($Y$3)+1,MATCH($J$1,Sheet2!$CF$2:$HF$2,0)+
COLUMN()-COLUMN($Y$3)))

Copy Y3 down as far as Y22, then copy Y3:Y22 across as far as required.

HTH
Steve D.

*press Ctrl+Shift+Enter instead of just Enter.



"Doug" wrote in message
...
I have a data validation dropdown list box in "sheet1" cell $J$1. Depending
on what I have selected in that dropdown list I would like it to match or
lookup the data with the respective columns the heading is over in
"sheet2".
The receiving array is "Sheet1" Y3:AA2284 and the sending array is
"Sheet2"
CF3:HE2284

Also, the column headers are merged for two or three columns each. I am
hoping there might be a way to pull the data from say all columns that the
header is over?

So to clarify, If I select the word "chair" from the dropdown, and "chair"
is over 2 columns CF & CG, then it will show all of those values. Likewise
if
there were 3 columns the heading was over it would return the values for
all
three columns.

If this requires very much work for you, please know that I very much
appreciate it, as this will really help me out.
--
Thank you!