Vlookup Based on Multiple Conditions

[Karthik]

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.

 

[Stefi]

It doesn't seem to be a Vlookup job!

=IF(AND(A1=E1,B1=F1),D1,"")

Regards!
Stefi



„Karthik†ezt írta:

 

[Karthik]

Thanks for your Kind response.

I've raw data in Col A through D which is over 45,000 rows and I enter data
in column E & F which is just 30 rows, and want column G to perform a lookup
to pick data from D if E & F are same as A & B.

 

[Ms-Exl-Learner]

Hi Karthik,

In G2 cell you have mentioned the required result is EMP2 just clarify me
that 10 B will comes under Emp1 then how it’s possible to get the Emp2 result
for G2 cell?

 

[Jacob Skaria]

Hi Karthik

Try the below formula in G1 and copy down as required

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX($D$1:$D$100,MATCH(1,($A$1:$A$100=E1)*($B$1:$B$100=F1),0))