VLOOKUP - Multiple Criteria

[gregula82]

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.

 

[barry houdini]

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

 

[gregula82]

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

 

[gregula82]

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.

 

[Keith412]

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.

 

[stanleydgromjr]

gregula82,

Excel Workbook
	A	B	C	D
1	ID	Type	Colour	Origin
2	66566	Apple	Green	South Africa
3	66567	Apple	Yellow	France
4	66568	Apple	Green	France
5	66588	Apple	Yellow	South Africa
Sheet1

Excel Workbook
	A	B	C	D
1	Type	Colour	Origin	Answer
2	Apple	Green	France	66568
Sheet2

Have a great day,
Stan

 

[barry houdini]

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...

 

[buttacup779]

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)))}

 

[AmitKohli]

Stan - thanks very much for the detailed example. helped a lot.
regards
amit kohli

 

[Amnesiac]

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.