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

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