Lookup values based on column headers?

[Doug]

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.

 

[stanleydgromjr]

Doug,

Please attach your workook.

Click on the *Reply* button, then scroll down and see *Manag
Attachments*.

 

[Doug]

There is no place to attach the workbook. Any suggestions or formulas to that
will work for what I described?

 

[Steve Dunn]

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]

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,ROW()-ROW($JO$3)+1,MATCH(Economy!$J$1,$EN$2:$JN$2,0)+COLUMN()-COLUMN($JO$3)))

 

[stunn]

Okay, I'll try this step by step, (please forgive me if it sounds
patronising): Start by putting something (e.g. "x", but absolutely
anything will do) into Data!JN2, this is so that the formula can
correctly deal with the last entry in your headings - it deduces the
number of columns by locating the next non-empty cell along.

Now copy this:

=IF((COLUMN()-COLUMN($JO$3)+1)>MIN(IF(OFFSET(
Data!$EN$2:$JN$2,,MATCH($J$1,Data!$EN$2:$JN$2,
0))<>"",COLUMN(Data!$EN$2:$JN$2)-CELL("COL",
Data!$EN$2:$JN$2)+1)),"",INDEX(Data!$EN$3:$JN$2284,
ROW()-ROW($JO$3)+1,MATCH($J$1,Data!$EN$2:$JN$2,0)+
COLUMN()-COLUMN($JO$3)))

then select cell Economy!JO3, and press the following key sequence:
F2, Ctrl+v, Ctrl+Shift+Enter, and you should see a curly bracket { in
front of the formula, (there's another at the end, but you won't see
that unless you expand the formula entry box).

Copy JO3 and paste it across JP3:JQ3. Now copy JO3:JQ3 and paste down
JO4:JQ2284. They will display #N/A until you put an appropriate value
into J1.

It will work with single columns without a problem.

HTH
Steve D.