VLOOKUP - Multiple Criteria

G

gregula82

New Member
Joined
Aug 18, 2006
Messages
8
Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.
 
Sort by date Sort by votes

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I get a huge honkin spreadsheet monthly from finance. I don't mind making changes to my copy, but I'll have to keep doing them. The spreadsheet looks like this (really pared down).
[TABLE="width: 488"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" span=9 width=54><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]jan-budget[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]jan-actual[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]feb-budget[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]feb-actual[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]……..[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-budget[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-actual[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q2-budget[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q2-actual[/TD]
[TD="class: xl68, width: 31, bgcolor: transparent"]…….[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Armenia[/TD]
[TD="class: xl66, bgcolor: transparent"]salary[/TD]
[TD="class: xl68, bgcolor: transparent"]123[/TD]
[TD="class: xl68, bgcolor: transparent"]124[/TD]
[TD="class: xl68, bgcolor: transparent"]223[/TD]
[TD="class: xl68, bgcolor: transparent"]224[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"]789[/TD]
[TD="class: xl68, bgcolor: transparent"]788[/TD]
[TD="class: xl68, bgcolor: transparent"]787[/TD]
[TD="class: xl68, bgcolor: transparent"]777[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl68, bgcolor: transparent"]23[/TD]
[TD="class: xl68, bgcolor: transparent"]24[/TD]
[TD="class: xl68, bgcolor: transparent"] 555[/TD]
[TD="class: xl68, bgcolor: transparent"]554[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] 445[/TD]
[TD="class: xl68, bgcolor: transparent"]446[/TD]
[TD="class: xl68, bgcolor: transparent"]448[/TD]
[TD="class: xl68, bgcolor: transparent"]447[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]training[/TD]
[TD="class: xl69, bgcolor: transparent"]187[/TD]
[TD="class: xl69, bgcolor: transparent"]162[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]185[/TD]
[TD="class: xl69, bgcolor: transparent"]168[/TD]
[TD="class: xl69, bgcolor: transparent"]166[/TD]
[TD="class: xl69, bgcolor: transparent"]199[/TD]
[TD="class: xl69, bgcolor: transparent"]192[/TD]
[TD="class: xl69, bgcolor: transparent"]168[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bulgaria[/TD]
[TD="class: xl66, bgcolor: transparent"]salary[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]169[/TD]
[TD="class: xl69, bgcolor: transparent"]182[/TD]
[TD="class: xl69, bgcolor: transparent"]190[/TD]
[TD="class: xl69, bgcolor: transparent"]198[/TD]
[TD="class: xl69, bgcolor: transparent"]190[/TD]
[TD="class: xl69, bgcolor: transparent"]180[/TD]
[TD="class: xl69, bgcolor: transparent"]195[/TD]
[TD="class: xl69, bgcolor: transparent"]166[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl69, bgcolor: transparent"]156[/TD]
[TD="class: xl69, bgcolor: transparent"]193[/TD]
[TD="class: xl69, bgcolor: transparent"]156[/TD]
[TD="class: xl69, bgcolor: transparent"]178[/TD]
[TD="class: xl69, bgcolor: transparent"]159[/TD]
[TD="class: xl69, bgcolor: transparent"]184[/TD]
[TD="class: xl69, bgcolor: transparent"]167[/TD]
[TD="class: xl69, bgcolor: transparent"]178[/TD]
[TD="class: xl69, bgcolor: transparent"]180[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]training[/TD]
[TD="class: xl69, bgcolor: transparent"]167[/TD]
[TD="class: xl69, bgcolor: transparent"]196[/TD]
[TD="class: xl69, bgcolor: transparent"]196[/TD]
[TD="class: xl69, bgcolor: transparent"]154[/TD]
[TD="class: xl69, bgcolor: transparent"]183[/TD]
[TD="class: xl69, bgcolor: transparent"]169[/TD]
[TD="class: xl69, bgcolor: transparent"]174[/TD]
[TD="class: xl69, bgcolor: transparent"]182[/TD]
[TD="class: xl69, bgcolor: transparent"]190[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]China[/TD]
[TD="class: xl66, bgcolor: transparent"]salary[/TD]
[TD="class: xl69, bgcolor: transparent"]198[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]189[/TD]
[TD="class: xl69, bgcolor: transparent"]181[/TD]
[TD="class: xl69, bgcolor: transparent"]164[/TD]
[TD="class: xl69, bgcolor: transparent"]174[/TD]
[TD="class: xl69, bgcolor: transparent"]181[/TD]
[TD="class: xl69, bgcolor: transparent"]169[/TD]
[TD="class: xl69, bgcolor: transparent"]181[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl69, bgcolor: transparent"]178[/TD]
[TD="class: xl69, bgcolor: transparent"]187[/TD]
[TD="class: xl69, bgcolor: transparent"]190[/TD]
[TD="class: xl69, bgcolor: transparent"]178[/TD]
[TD="class: xl69, bgcolor: transparent"]188[/TD]
[TD="class: xl69, bgcolor: transparent"]168[/TD]
[TD="class: xl69, bgcolor: transparent"]156[/TD]
[TD="class: xl69, bgcolor: transparent"]186[/TD]
[TD="class: xl69, bgcolor: transparent"]156[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]training[/TD]
[TD="class: xl69, bgcolor: transparent"]187[/TD]
[TD="class: xl69, bgcolor: transparent"]171[/TD]
[TD="class: xl69, bgcolor: transparent"]165[/TD]
[TD="class: xl69, bgcolor: transparent"]152[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]186[/TD]
[TD="class: xl69, bgcolor: transparent"]158[/TD]
[TD="class: xl69, bgcolor: transparent"]186[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Denmark[/TD]
[TD="class: xl66, bgcolor: transparent"]salary[/TD]
[TD="class: xl69, bgcolor: transparent"]198[/TD]
[TD="class: xl69, bgcolor: transparent"]152[/TD]
[TD="class: xl69, bgcolor: transparent"]168[/TD]
[TD="class: xl69, bgcolor: transparent"]194[/TD]
[TD="class: xl69, bgcolor: transparent"]199[/TD]
[TD="class: xl69, bgcolor: transparent"]176[/TD]
[TD="class: xl69, bgcolor: transparent"]184[/TD]
[TD="class: xl69, bgcolor: transparent"]189[/TD]
[TD="class: xl69, bgcolor: transparent"]193[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl69, bgcolor: transparent"]167[/TD]
[TD="class: xl69, bgcolor: transparent"]194[/TD]
[TD="class: xl69, bgcolor: transparent"]194[/TD]
[TD="class: xl69, bgcolor: transparent"]167[/TD]
[TD="class: xl69, bgcolor: transparent"]176[/TD]
[TD="class: xl69, bgcolor: transparent"]165[/TD]
[TD="class: xl69, bgcolor: transparent"]174[/TD]
[TD="class: xl69, bgcolor: transparent"]154[/TD]
[TD="class: xl69, bgcolor: transparent"]158[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]training[/TD]
[TD="class: xl69, bgcolor: transparent"]173[/TD]
[TD="class: xl69, bgcolor: transparent"]189[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]175[/TD]
[TD="class: xl69, bgcolor: transparent"]196[/TD]
[TD="class: xl69, bgcolor: transparent"]197[/TD]
[TD="class: xl69, bgcolor: transparent"]151[/TD]
[TD="class: xl69, bgcolor: transparent"]169[/TD]
[TD="class: xl69, bgcolor: transparent"]192[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Equador[/TD]
[TD="class: xl66, bgcolor: transparent"]salary[/TD]
[TD="class: xl69, bgcolor: transparent"]192[/TD]
[TD="class: xl69, bgcolor: transparent"]188[/TD]
[TD="class: xl69, bgcolor: transparent"]195[/TD]
[TD="class: xl69, bgcolor: transparent"]191[/TD]
[TD="class: xl69, bgcolor: transparent"]186[/TD]
[TD="class: xl69, bgcolor: transparent"]168[/TD]
[TD="class: xl69, bgcolor: transparent"]165[/TD]
[TD="class: xl69, bgcolor: transparent"]175[/TD]
[TD="class: xl69, bgcolor: transparent"]164[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl69, bgcolor: transparent"]200[/TD]
[TD="class: xl69, bgcolor: transparent"]200[/TD]
[TD="class: xl69, bgcolor: transparent"]184[/TD]
[TD="class: xl69, bgcolor: transparent"]183[/TD]
[TD="class: xl69, bgcolor: transparent"]169[/TD]
[TD="class: xl69, bgcolor: transparent"]156[/TD]
[TD="class: xl69, bgcolor: transparent"]159[/TD]
[TD="class: xl69, bgcolor: transparent"]187[/TD]
[TD="class: xl69, bgcolor: transparent"]187[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]training[/TD]
[TD="class: xl69, bgcolor: transparent"]163[/TD]
[TD="class: xl69, bgcolor: transparent"]166[/TD]
[TD="class: xl69, bgcolor: transparent"]169[/TD]
[TD="class: xl69, bgcolor: transparent"]198[/TD]
[TD="class: xl69, bgcolor: transparent"]172[/TD]
[TD="class: xl69, bgcolor: transparent"]160[/TD]
[TD="class: xl69, bgcolor: transparent"]178[/TD]
[TD="class: xl69, bgcolor: transparent"]199[/TD]
[TD="class: xl69, bgcolor: transparent"]187[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

The group managers would like a report that picks out specific pieces from this and in the form of:
[TABLE="width: 185"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]q1-budget[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-actual[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Armenia[/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bulgaria[/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]China[/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Denmark[/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Equador[/TD]
[TD="class: xl66, bgcolor: transparent"]travel[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

Say for example they want the data for travel in Q1. How do I get to that data? I don't mind adding hidden columns or whatever. Been all around VLOOKUP and can't seem to get beyond N/A.
thanks,
Rockfish
 
Upvote 0
Rockfish said:
I get a huge honkin spreadsheet monthly from finance. I don't mind making changes to my copy, but I'll have to keep doing them. The spreadsheet looks like this (really pared down).
[TABLE="width: 488"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]jan-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]jan-actual
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]feb-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]feb-actual
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]……..
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-actual
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q2-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q2-actual
[/TD]
[TD="class: xl68, width: 31, bgcolor: transparent"]…….
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Armenia
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl68, bgcolor: transparent"]123
[/TD]
[TD="class: xl68, bgcolor: transparent"]124
[/TD]
[TD="class: xl68, bgcolor: transparent"]223
[/TD]
[TD="class: xl68, bgcolor: transparent"]224
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]789
[/TD]
[TD="class: xl68, bgcolor: transparent"]788
[/TD]
[TD="class: xl68, bgcolor: transparent"]787
[/TD]
[TD="class: xl68, bgcolor: transparent"]777
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl68, bgcolor: transparent"]23
[/TD]
[TD="class: xl68, bgcolor: transparent"]24
[/TD]
[TD="class: xl68, bgcolor: transparent"] 555
[/TD]
[TD="class: xl68, bgcolor: transparent"]554
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"] 445
[/TD]
[TD="class: xl68, bgcolor: transparent"]446
[/TD]
[TD="class: xl68, bgcolor: transparent"]448
[/TD]
[TD="class: xl68, bgcolor: transparent"]447
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]162
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]185
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]166
[/TD]
[TD="class: xl69, bgcolor: transparent"]199
[/TD]
[TD="class: xl69, bgcolor: transparent"]192
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bulgaria
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]182
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl69, bgcolor: transparent"]180
[/TD]
[TD="class: xl69, bgcolor: transparent"]195
[/TD]
[TD="class: xl69, bgcolor: transparent"]166
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]193
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]159
[/TD]
[TD="class: xl69, bgcolor: transparent"]184
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]180
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]196
[/TD]
[TD="class: xl69, bgcolor: transparent"]196
[/TD]
[TD="class: xl69, bgcolor: transparent"]154
[/TD]
[TD="class: xl69, bgcolor: transparent"]183
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]174
[/TD]
[TD="class: xl69, bgcolor: transparent"]182
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]China
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]189
[/TD]
[TD="class: xl69, bgcolor: transparent"]181
[/TD]
[TD="class: xl69, bgcolor: transparent"]164
[/TD]
[TD="class: xl69, bgcolor: transparent"]174
[/TD]
[TD="class: xl69, bgcolor: transparent"]181
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]181
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]188
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]171
[/TD]
[TD="class: xl69, bgcolor: transparent"]165
[/TD]
[TD="class: xl69, bgcolor: transparent"]152
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl69, bgcolor: transparent"]158
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Denmark
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]152
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]194
[/TD]
[TD="class: xl69, bgcolor: transparent"]199
[/TD]
[TD="class: xl69, bgcolor: transparent"]176
[/TD]
[TD="class: xl69, bgcolor: transparent"]184
[/TD]
[TD="class: xl69, bgcolor: transparent"]189
[/TD]
[TD="class: xl69, bgcolor: transparent"]193
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]194
[/TD]
[TD="class: xl69, bgcolor: transparent"]194
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]176
[/TD]
[TD="class: xl69, bgcolor: transparent"]165
[/TD]
[TD="class: xl69, bgcolor: transparent"]174
[/TD]
[TD="class: xl69, bgcolor: transparent"]154
[/TD]
[TD="class: xl69, bgcolor: transparent"]158
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]173
[/TD]
[TD="class: xl69, bgcolor: transparent"]189
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]175
[/TD]
[TD="class: xl69, bgcolor: transparent"]196
[/TD]
[TD="class: xl69, bgcolor: transparent"]197
[/TD]
[TD="class: xl69, bgcolor: transparent"]151
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]192
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Equador
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]192
[/TD]
[TD="class: xl69, bgcolor: transparent"]188
[/TD]
[TD="class: xl69, bgcolor: transparent"]195
[/TD]
[TD="class: xl69, bgcolor: transparent"]191
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]165
[/TD]
[TD="class: xl69, bgcolor: transparent"]175
[/TD]
[TD="class: xl69, bgcolor: transparent"]164
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]200
[/TD]
[TD="class: xl69, bgcolor: transparent"]200
[/TD]
[TD="class: xl69, bgcolor: transparent"]184
[/TD]
[TD="class: xl69, bgcolor: transparent"]183
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]159
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]166
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]172
[/TD]
[TD="class: xl69, bgcolor: transparent"]160
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]199
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

The group managers would like a report that picks out specific pieces from this and in the form of:
[TABLE="width: 185"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]q1-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-actual
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Armenia
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bulgaria
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]China
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Denmark
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Equador
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Say for example they want the data for travel in Q1. How do I get to that data? I don't mind adding hidden columns or whatever. Been all around VLOOKUP and can't seem to get beyond N/A.
thanks,
Rockfish
Click to expand...

Let A1:L16 on Sheet1 house the data exhbit.

Let A:D on Sheet2 house the processing as shown below:
[TABLE="width: 333"]
<colgroup><col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3498;" width="98"> <col style="width: 91pt; mso-width-source: userset; mso-width-alt: 4295;" width="121"> <col style="width: 73pt; mso-width-source: userset; mso-width-alt: 3470;" width="98"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4494;" width="126"> <tbody>[TR]
[TD="class: xl64, width: 98, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 121, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 98, bgcolor: transparent"]q1-budget[/TD]
[TD="class: xl63, width: 126, bgcolor: transparent"]q1-actual[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: transparent"]Armenia[/TD]
[TD="class: xl64, width: 121, bgcolor: transparent"]travel[/TD]
[TD="class: xl63, width: 98, bgcolor: transparent"]445[/TD]
[TD="class: xl63, width: 126, bgcolor: transparent"]446[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: transparent"]Bulgaria[/TD]
[TD="class: xl64, width: 121, bgcolor: transparent"]travel[/TD]
[TD="class: xl63, width: 98, bgcolor: transparent"]184[/TD]
[TD="class: xl63, width: 126, bgcolor: transparent"]167[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: transparent"]China[/TD]
[TD="class: xl64, width: 121, bgcolor: transparent"]travel[/TD]
[TD="class: xl63, width: 98, bgcolor: transparent"]168[/TD]
[TD="class: xl63, width: 126, bgcolor: transparent"]156[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: transparent"]Denmark[/TD]
[TD="class: xl64, width: 121, bgcolor: transparent"]travel[/TD]
[TD="class: xl63, width: 98, bgcolor: transparent"]165[/TD]
[TD="class: xl63, width: 126, bgcolor: transparent"]174[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 98, bgcolor: transparent"]Equador[/TD]
[TD="class: xl64, width: 121, bgcolor: transparent"]travel[/TD]
[TD="class: xl63, width: 98, bgcolor: transparent"]156[/TD]
[TD="class: xl63, width: 126, bgcolor: transparent"]159[/TD]
[/TR]
</tbody>[/TABLE]


C2, copy across and down:
Rich (BB code): 
=INDEX(Sheet1!$C$2:$L$16,
  MATCH($A2,Sheet1!$A$2:$A$16,0)+
   (MATCH($B2,{"salary","Travel","training"},0)-1),
  MATCH(C$1,Sheet1!$C$1:$L$1,0))
 
Upvote 0
If you have your data in A2:D5, and the search items in A11:C11, then you can use this:
=INDEX(A2:A5,MATCH(A11&B11&C11,B2:B5&C2:C5&D2:D5,)) enter with Ctrl+Shift+Enter
 
Upvote 0
soolsey said:
This makes sense to me now. You can have any number greater than 1 as the lookup value because that will always be greater than the last referenced value in 1/everything that is looked up.
...
Click to expand...

I'm afraid this is not exactly right. The underlying binary search, when the value it searches for is large, lands necessarily on the last cell with a numeric value even if it's very big or very small. The mechanics are given here:

http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (post #7)

Large means one that possibly cannot occur in the reference of interest.
 
Last edited:
Upvote 0
Aladin Akyurek said:
I'm afraid this is not exactly right..
Click to expand...
No, it IS exactly right.

If the lookup value is larger than any numeric value in the lookup vector then the result of the formula will be the last numeric value in the lookup vector.
 
Upvote 0
Rockfish said:
I get a huge honkin spreadsheet monthly from finance. I don't mind making changes to my copy, but I'll have to keep doing them. The spreadsheet looks like this (really pared down).
[TABLE="width: 488"]
<TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]jan-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]jan-actual
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]feb-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]feb-actual
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]……..
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-actual
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q2-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q2-actual
[/TD]
[TD="class: xl68, width: 31, bgcolor: transparent"]…….
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Armenia
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl68, bgcolor: transparent"]123
[/TD]
[TD="class: xl68, bgcolor: transparent"]124
[/TD]
[TD="class: xl68, bgcolor: transparent"]223
[/TD]
[TD="class: xl68, bgcolor: transparent"]224
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]789
[/TD]
[TD="class: xl68, bgcolor: transparent"]788
[/TD]
[TD="class: xl68, bgcolor: transparent"]787
[/TD]
[TD="class: xl68, bgcolor: transparent"]777
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl68, bgcolor: transparent"]23
[/TD]
[TD="class: xl68, bgcolor: transparent"]24
[/TD]
[TD="class: xl68, bgcolor: transparent"]555
[/TD]
[TD="class: xl68, bgcolor: transparent"]554
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]445
[/TD]
[TD="class: xl68, bgcolor: transparent"]446
[/TD]
[TD="class: xl68, bgcolor: transparent"]448
[/TD]
[TD="class: xl68, bgcolor: transparent"]447
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]162
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]185
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]166
[/TD]
[TD="class: xl69, bgcolor: transparent"]199
[/TD]
[TD="class: xl69, bgcolor: transparent"]192
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bulgaria
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]182
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl69, bgcolor: transparent"]180
[/TD]
[TD="class: xl69, bgcolor: transparent"]195
[/TD]
[TD="class: xl69, bgcolor: transparent"]166
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]193
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]159
[/TD]
[TD="class: xl69, bgcolor: transparent"]184
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]180
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]196
[/TD]
[TD="class: xl69, bgcolor: transparent"]196
[/TD]
[TD="class: xl69, bgcolor: transparent"]154
[/TD]
[TD="class: xl69, bgcolor: transparent"]183
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]174
[/TD]
[TD="class: xl69, bgcolor: transparent"]182
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]China
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]189
[/TD]
[TD="class: xl69, bgcolor: transparent"]181
[/TD]
[TD="class: xl69, bgcolor: transparent"]164
[/TD]
[TD="class: xl69, bgcolor: transparent"]174
[/TD]
[TD="class: xl69, bgcolor: transparent"]181
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]181
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]190
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]188
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]171
[/TD]
[TD="class: xl69, bgcolor: transparent"]165
[/TD]
[TD="class: xl69, bgcolor: transparent"]152
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl69, bgcolor: transparent"]158
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Denmark
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]152
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]194
[/TD]
[TD="class: xl69, bgcolor: transparent"]199
[/TD]
[TD="class: xl69, bgcolor: transparent"]176
[/TD]
[TD="class: xl69, bgcolor: transparent"]184
[/TD]
[TD="class: xl69, bgcolor: transparent"]189
[/TD]
[TD="class: xl69, bgcolor: transparent"]193
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]194
[/TD]
[TD="class: xl69, bgcolor: transparent"]194
[/TD]
[TD="class: xl69, bgcolor: transparent"]167
[/TD]
[TD="class: xl69, bgcolor: transparent"]176
[/TD]
[TD="class: xl69, bgcolor: transparent"]165
[/TD]
[TD="class: xl69, bgcolor: transparent"]174
[/TD]
[TD="class: xl69, bgcolor: transparent"]154
[/TD]
[TD="class: xl69, bgcolor: transparent"]158
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]173
[/TD]
[TD="class: xl69, bgcolor: transparent"]189
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]175
[/TD]
[TD="class: xl69, bgcolor: transparent"]196
[/TD]
[TD="class: xl69, bgcolor: transparent"]197
[/TD]
[TD="class: xl69, bgcolor: transparent"]151
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]192
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Equador
[/TD]
[TD="class: xl66, bgcolor: transparent"]salary
[/TD]
[TD="class: xl69, bgcolor: transparent"]192
[/TD]
[TD="class: xl69, bgcolor: transparent"]188
[/TD]
[TD="class: xl69, bgcolor: transparent"]195
[/TD]
[TD="class: xl69, bgcolor: transparent"]191
[/TD]
[TD="class: xl69, bgcolor: transparent"]186
[/TD]
[TD="class: xl69, bgcolor: transparent"]168
[/TD]
[TD="class: xl69, bgcolor: transparent"]165
[/TD]
[TD="class: xl69, bgcolor: transparent"]175
[/TD]
[TD="class: xl69, bgcolor: transparent"]164
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl69, bgcolor: transparent"]200
[/TD]
[TD="class: xl69, bgcolor: transparent"]200
[/TD]
[TD="class: xl69, bgcolor: transparent"]184
[/TD]
[TD="class: xl69, bgcolor: transparent"]183
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]156
[/TD]
[TD="class: xl69, bgcolor: transparent"]159
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]training
[/TD]
[TD="class: xl69, bgcolor: transparent"]163
[/TD]
[TD="class: xl69, bgcolor: transparent"]166
[/TD]
[TD="class: xl69, bgcolor: transparent"]169
[/TD]
[TD="class: xl69, bgcolor: transparent"]198
[/TD]
[TD="class: xl69, bgcolor: transparent"]172
[/TD]
[TD="class: xl69, bgcolor: transparent"]160
[/TD]
[TD="class: xl69, bgcolor: transparent"]178
[/TD]
[TD="class: xl69, bgcolor: transparent"]199
[/TD]
[TD="class: xl69, bgcolor: transparent"]187
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

The group managers would like a report that picks out specific pieces from this and in the form of:
[TABLE="width: 185"]
<TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]q1-budget
[/TD]
[TD="class: xl67, width: 54, bgcolor: transparent"]q1-actual
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Armenia
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bulgaria
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]China
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Denmark
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Equador
[/TD]
[TD="class: xl66, bgcolor: transparent"]travel
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

Say for example they want the data for travel in Q1. How do I get to that data? I don't mind adding hidden columns or whatever. Been all around VLOOKUP and can't seem to get beyond N/A.
thanks,
Rockfish
Click to expand...
Have you considered using Autofilter?
 
Upvote 0
Thank you for your insight. Have been working with this all day and it works great in the example I provided. Turns out I provided an oversimplified example and that breaks the solution all to pieces. The "huge honkin" budget sheet is actuall of the form
[TABLE="width: 476"]
<COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY>[TR]
[TD="class: xl78, width: 49, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 53, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 56, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 49, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 53, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 56, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 49, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 53, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 56, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 49, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 53, bgcolor: transparent"] [/TD]
[TD="class: xl78, width: 56, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: yellow"] Jun [/TD]
[TD="class: xl79, bgcolor: yellow"] Jun [/TD]
[TD="class: xl79, bgcolor: yellow"] Jun [/TD]
[TD="class: xl79, bgcolor: yellow"] Jul [/TD]
[TD="class: xl79, bgcolor: yellow"] Jul [/TD]
[TD="class: xl79, bgcolor: yellow"] Jul [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Q1 [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Q1 [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Q1 [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Q2 [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Q2 [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Q2 [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: yellow"] Actual [/TD]
[TD="class: xl79, bgcolor: yellow"] Budget [/TD]
[TD="class: xl79, bgcolor: yellow"] Commit [/TD]
[TD="class: xl79, bgcolor: yellow"] Actual [/TD]
[TD="class: xl79, bgcolor: yellow"] Budget [/TD]
[TD="class: xl79, bgcolor: yellow"] Commit [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Actual [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Budget [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Commit [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Actual [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Budget [/TD]
[TD="class: xl80, bgcolor: #b7dee8"] Commit [/TD]
[/TR]
</TBODY>[/TABLE]
Taking three rows to do what I represented at one row and throwing another bunch of non-unique values into the mix. Row one is used for filters.
Is it still possible to pick the desired values from the spreadsheet?
Thanks and sorry for the mistake.
Rockfish
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
102
Rokcmd
R
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
240
acool
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
447
Peter_SSs
Peter_SSs
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
165
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,923
Messages
6,175,389
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top