VLOOKUP based on two criteria

[kumawat_s]

I am looking a function (may be vlookup) which can provide the value based on two conditions.

I am attaching a xls file.

When I use vlookup based on column A it gives wrong value because column A value are multiple in list.

If I use use colun C the same problem is there.

How can I use VLOOKUP (or any other function) based on two criteria (first column A and then column C) and get the column D value (batch)

I am using XP.

Please reply urgently as my boss has given me limited time


thanks

[Miraun]

Quick immediate response:
use Concatenate on the two values, and use Concatenate on the reference data, repeat the vlookup...

I'll post a response to sample workbook in a moment.

[Azam Ali]

find the attatche file

note: array formula is used and should be confirmed thorugh Ctrl+Shift+Enter instead of simple enter

[Azam Ali]

U may also use as suggested by miraun

=INDEX(B!$D$3:$D$19,MATCH(A2&C2,INDEX(B!$A$3:$A$19&B!$C$3:$C$19,0),0)) and copy down

[sunflowers]

This try :
find the attatche file
Using ISNA function to remove the error # N/A :

[Miraun]

The two tables provided are different dates... one is January, one is April. Originally I thought that it was a date formatting issue, but with the reference data provided, there's limited linkage.

Azam's suggestion works absolutely perfectly if you cannot, or do not wish to, add the column to the reference data.

Array formulas tend to be slightly more processor intensive than a function such as a vlookup, which solution you should use is completely up to you, based upon how much data you have, and whether or not it would be problematic to add in the concatenated key value to the reference data. It's kind of choosing the lesser of two evils, but only you can make that decision.

And thanks for your solutions Azam... I rarely work with the array function, it's a great way to compare and learn. On each of these similar threads!

This is excel, ask one question, and get 5 solutions!

[sunflowers]

If you want to use VLOOKUP funtion and not use array fomular funtion, You can use this file.