Vlookup with two criteria

**mkvassh** · 10-22-2009, 07:22 AM

Is there a way to do a vlookup with two separate criteria?

**zbor** · 10-22-2009, 07:26 AM

It can be made. Post an example

**mkvassh** · 10-22-2009, 07:42 AM

I have the following content in column A, B and C

Cost element Cost object Area
709015 WTK.RND.0360.EN.EX Other
972236 WTK.RND.0360.EN.EX Drilling & Production
972261 WTK.RND.0360.EN.EX Drilling & Production
979455 WTK.RND.0360.EN.EX Upstream Technology
709015 WTK.RND.0360.EN.PR Other
972237 WTK.RND.0360.EN.PR Drilling & Production
972260 WTK.RND.0360.EN.PR Drilling & Production
977745 WTK.RND.0360.EN.PR Project Development
979456 WTK.RND.0360.EN.PR Upstream Technology
979508 WTK.RND.0360.EN.PR Other
709015 WTK.RND.0371.EN.EX Credit
709015 WTK.RND.0371.EN.PR Credit

I want to lookup the content of column C if there is match with the content in A and B combined. Lookup values for A and B will be in Z1 and Z2. An array function is OK.

**NBVC** · 10-22-2009, 07:48 AM

Try

=Index($C$1:$C$100,Match(1,Index(($A$1:$A$100=Z1)*($B$1:$B$100=Z2),0),0))

adjust ranges to suit

**mkvassh** · 10-22-2009, 07:56 AM

It works. Is it not possible to do this with a Vlookup-function?

**NBVC** · 10-22-2009, 08:00 AM

It is, but you would need to have a helper column inbetween, which concatenates columns A and B, and then you can use something like

=Vlookup(Z1&Z2,$C$1:$D$100,2,0)

where column C contains concatenated columns A&B and Column D is the column to index.

**mkvassh** · 10-22-2009, 08:02 AM

Thank you :-)

**mkvassh** · 10-22-2009, 08:21 AM

I found a Vlookup array function on Internet. It will work as well

=VLOOKUP(E1,IF($B$1:$B$2=F1,$A1:$C$2,""),3,FALSE)

Press CTRL-SHIFT-ENTER together to enter this.

Click here if you want to check the place I found this. I haven't adjusted the function to fit the problem above.