Vlookup with more than 1 condition

[yael]

Hi,

Is there any way to use Vlookup for more than 1 parameter, or is there a similar function that i can use?
For example I have columns showing Client name, client ID, client type, and location. I want to lookup the location of a specific client name + ID + type. One client name can have a few different IDs and types, so I need to be able to look for all 3 parameters.

Thx!

[micope21]

Hi
Sheet1
A2 Client name
B2 ID
C2 Type

Sheet2
A2 Client name
B2 ID
C2 Type
D2 Location

Try this =IFERROR(INDEX(Sheet2!$D$2:$D$100,MATCH(1,(A2=Sheet2!$A$2:$A$100)*(B2=Sheet2!$B$2:$B$100)*(C2=Sheet2!$C$2:$C$100),0)),"None!") Control+Shift+Enter together.
If you don't want None in? )),"None!") remove to this )),"")

[yael]

Hi,

Thx for your quick reply. I can't get it to work though. I tried what you suggested (see excel attached) but it doesn't return the correct values. Can you take a look and see what I'm doing wrong?

Thx
Yael.

[zbor]

That's because you didn't comfirmed it with ctrl+shift+enter (instead of just enter) as micope21 told you.

Here is non-array solution (normal enter): =INDEX(Sheet2!$D$2:$D$100, MATCH(A2& B2 & C2, INDEX(Sheet2!$A$2:$A$100 & Sheet2!$B$2:$B$100 &Sheet2!$C$2:$C$100, 0), 0))

[micope21]

Hi

You need to do control+shift+enter to get the results.


There Zbor formula without array.

Reason I did this was put in none as sometime no results become none.

Your choice.

Micope21

[yael]

Thankyou both for your help!

[micope21]

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Cheers