If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Vlookup Based on Multiple Conditions
Base formula:
=INDEX(D,sumproduct(--(A$1:A$100=E1),--(B$1:B$100=F1),ROW(D$1$100))) To make blank if no match is found: =IF(sumproduct(--(A$1:A$100=E1),--(B$1:B$100=F1))=0,"",INDEX(D,sumproduct(--(A$1:A$100=E1),--(B$1:B$100=F1),ROW(D$1$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 |
Thread Tools | |
Display Modes | |
|
|