VLOOKUP - Multiple Criteria

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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You can't use VLOOKUP like that but you can use a different formula, e.g. to find the first row where the criteria is met in columns A, B and C and then return the value from D

=INDEX(D1:D100,MATCH(1,(A1:A100="x")*(B1:B100="y")*(C1:C100="z"),0))

confirmed with CTRL+SHIFT+ENTER
 
Upvote 0
Hi,

This is good, but it doesnt quite return the result I am looking for.

ID Type Colour Origin
66566 Apple Green South Africa
66567 Apple Yellow France
66568 Apple Green France
66588 Apple Yellow South Africa

I don't know how to make an excel table appear here. This is best I could do sorry.

What I am trying to do is pull the correct ID into another worksheet tab, based on the three criteria's of Type, Colour and Origin. Obviously you have said that a vlookup cannot do this. The match looks good but I can't seem to get it to extract the ids into the other tab.

Does this make a bit more sense?

Thanks for your help so far.

Greg
 
Upvote 0
My apologies,

I have got it to work now, just needed a few tweaks.

Also - One thing...the ctrl+shift+enter.....

If I am using this within an automated process (as in each month it will pick this up when the list of articles changes), does this mean that they won't work??

Cheers,

Greg.
 
Upvote 0
I had a somewhat similar problem where i wanted to use Vlookup to find a specific security and specific identifier of where it was held and then return the number of shares if both the other arguments held true.

For example
"111111" and "Statestreetbank" ; i just concatenated them together and it works for the purpose of what i was trying to do and made the vlookup work to return the # of shares.
 
Upvote 0
gregula82,

Excel Workbook
ABCD
1IDTypeColourOrigin
266566AppleGreenSouth Africa
366567AppleYellowFrance
466568AppleGreenFrance
566588AppleYellowSouth Africa
Sheet1



Excel Workbook
ABCD
1TypeColourOriginAnswer
2AppleGreenFrance66568
Sheet2



Have a great day,
Stan
 
Upvote 0
Hello Greg,

Of course Keith's right. If you use a helper column with your three criteria columns concatenated then you can use VLOOKUP....or to avoid CSE you can try a variation on the formula I posted initially

=LOOKUP(2,1/((A1:A100="x")*(B1:B100="y")*(C1:C100="z")),D1:D100)

This differs from the INDEX/MATCH approach because it will give the value from column D on the last row where all 3 criteria are satisfied, rather than the first. If you will only ever have 1 row which matches all 3 then that shouldn't make any difference...
 
Upvote 0
Yes, just tried it, and the dates are not coming up, still #N/A

{=INDEX('This Week'!A1:A1000,MATCH(1,('Last Week'!A4='This Week'!A4)*('Last Week'!B4='This Week'!B4)*('Last Week'!C4='This Week'!C4)))}
 
Upvote 0
You can use the Index and Match functions but I believe it is limited to 55,000 configurations. You can also combine the 3 variable into one variable and use vlookup. Try this.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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