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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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).
jan-budgetjan-actualfeb-budgetfeb-actual……..q1-budgetq1-actualq2-budgetq2-actual…….
Armeniasalary123124223224 789788787777
travel2324 555554 445446448447
training187162163185168166199192168
Bulgariasalary163169182190198190180195166
travel156193156178159184167178180
training167196196154183169174182190
Chinasalary198163189181164174181169181
travel178187190178188168156186156
training187171165152163163186158186
Denmarksalary198152168194199176184189193
travel167194194167176165174154158
training173189163175196197151169192
Equadorsalary192188195191186168165175164
travel200200184183169156159187187
training163166169198172160178199187

<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>
</TBODY>

The group managers would like a report that picks out specific pieces from this and in the form of:
q1-budgetq1-actual
Armeniatravel
Bulgariatravel
Chinatravel
Denmarktravel
Equadortravel

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><TBODY>
</TBODY>

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).
jan-budget
jan-actual
feb-budget
feb-actual
……..
q1-budget
q1-actual
q2-budget
q2-actual
…….
Armenia
salary
123
124
223
224
789
788
787
777
travel
23
24
 555
554
 445
446
448
447
training
187
162
163
185
168
166
199
192
168
Bulgaria
salary
163
169
182
190
198
190
180
195
166
travel
156
193
156
178
159
184
167
178
180
training
167
196
196
154
183
169
174
182
190
China
salary
198
163
189
181
164
174
181
169
181
travel
178
187
190
178
188
168
156
186
156
training
187
171
165
152
163
163
186
158
186
Denmark
salary
198
152
168
194
199
176
184
189
193
travel
167
194
194
167
176
165
174
154
158
training
173
189
163
175
196
197
151
169
192
Equador
salary
192
188
195
191
186
168
165
175
164
travel
200
200
184
183
169
156
159
187
187
training
163
166
169
198
172
160
178
199
187

<tbody>
</tbody>

The group managers would like a report that picks out specific pieces from this and in the form of:
q1-budget
q1-actual
Armenia
travel
Bulgaria
travel
China
travel
Denmark
travel
Equador
travel

<tbody>
</tbody>

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:
q1-budgetq1-actual
Armeniatravel445446
Bulgariatravel184167
Chinatravel168156
Denmarktravel165174
Equadortravel156159

<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>
</tbody>


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).
jan-budget
jan-actual
feb-budget
feb-actual
……..
q1-budget
q1-actual
q2-budget
q2-actual
…….
Armenia
salary
123
124
223
224
789
788
787
777
travel
23
24
555
554
445
446
448
447
training
187
162
163
185
168
166
199
192
168
Bulgaria
salary
163
169
182
190
198
190
180
195
166
travel
156
193
156
178
159
184
167
178
180
training
167
196
196
154
183
169
174
182
190
China
salary
198
163
189
181
164
174
181
169
181
travel
178
187
190
178
188
168
156
186
156
training
187
171
165
152
163
163
186
158
186
Denmark
salary
198
152
168
194
199
176
184
189
193
travel
167
194
194
167
176
165
174
154
158
training
173
189
163
175
196
197
151
169
192
Equador
salary
192
188
195
191
186
168
165
175
164
travel
200
200
184
183
169
156
159
187
187
training
163
166
169
198
172
160
178
199
187

<TBODY>
</TBODY>

The group managers would like a report that picks out specific pieces from this and in the form of:
q1-budget
q1-actual
Armenia
travel
Bulgaria
travel
China
travel
Denmark
travel
Equador
travel

<TBODY>
</TBODY>

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
Jun Jun Jun Jul Jul Jul Q1 Q1 Q1 Q2 Q2 Q2
Actual Budget Commit Actual Budget Commit Actual Budget Commit Actual Budget Commit

<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>
</TBODY>
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

P
  • Solved
Vlookup on Two matches
Replies
6
Views
137
Alex Blakenburg
A
excelbytes
  • Solved
COUNTIFS With Lists Of Multiple Criteria
Replies
5
Views
323
Fluff
Fluff
N
  • Question
MATCH and INDEX help
Replies
3
Views
242
etaf
etaf
A
  • Solved
Sumif using Multiple Criteria
Replies
4
Views
349
acool
A
A
  • Solved
Multiple Criteria in If function Returns error
Replies
8
Views
160
etaf
etaf

Forum statistics

Threads
1,216,122
Messages
6,128,964
Members
449,480
Latest member
yesitisasport

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