ExcelBanter - Vlookup Based on Multiple Conditions

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Vlookup Based on Multiple Conditions (https://www.excelbanter.com/excel-discussion-misc-queries/249964-vlookup-based-multiple-conditions.html)

Vlookup Based on Multiple Conditions

Hi All,

Is there any way to put a Vlookup formula with multiple criterias?

I've over 45,000 rows in Col 'A' - 'E'.
I want G1 to return value of D1, if (E1 and F1) = (A1 and B1) respectively
and blank if they do not match.

A B C D E F G
10 A X Emp1 10 A Emp1
10 B X Emp1 10 B EMP2
11 A Y Emp1 12 C Emp1
11 C Y Emp2
12 A X Emp2
12 A X Emp1
12 C Y Emp1

Thanks for your help.
--
Karthi

Vlookup Based on Multiple Conditions

Base formula:
=INDEX(D:D,sumproduct(--(A$1:A$100=E1),--(B$1:B$100=F1),ROW(D$1:D$100)))

To make blank if no match is found:
=IF(sumproduct(--(A$1:A$100=E1),--(B$1:B$100=F1))=0,"",INDEX(D:D,sumproduct(--(A$1:A$100=E1),--(B$1:B$100=F1),ROW(D$1:D$100))))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Karthik" wrote:

Hi All,

Is there any way to put a Vlookup formula with multiple criterias?

I've over 45,000 rows in Col 'A' - 'E'.
I want G1 to return value of D1, if (E1 and F1) = (A1 and B1) respectively
and blank if they do not match.

A B C D E F G
10 A X Emp1 10 A Emp1
10 B X Emp1 10 B EMP2
11 A Y Emp1 12 C Emp1
11 C Y Emp2
12 A X Emp2
12 A X Emp1
12 C Y Emp1

Thanks for your help.
--
Karthi