# VLOOKUP - Multiple Criteria

This is a discussion on VLOOKUP - Multiple Criteria within the Excel Questions forums, part of the Question Forums category; Hi Everyone, I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. ...

1. ## VLOOKUP - Multiple Criteria

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.

2. ## Re: VLOOKUP - Multiple Criteria

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

3. ## Re: VLOOKUP - Multiple Criteria

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

4. ## Re: VLOOKUP - Multiple Criteria

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.

5. ## Re: VLOOKUP - Multiple Criteria

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.

6. ## Re: VLOOKUP - Multiple Criteria

gregula82,

Sheet1

 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

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

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

 Cell Formula D2 {=INDEX(Sheet1!A1:A1000,MATCH(1,(Sheet1!B1:B1000=\$A2)*(Sheet1!C1:C1000=\$B2)*(Sheet1!D1:D1000=\$C2),0))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

Have a great day,
Stan

7. ## Re: VLOOKUP - Multiple Criteria

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

8. ## Re: VLOOKUP - Multiple Criteria

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

9. ## Re: VLOOKUP - Multiple Criteria

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

10. ## Re: VLOOKUP - Multiple Criteria

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.

Page 1 of 15 12311 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•