Vlookup with condition

[ravanan]

C1 c2

1 L2
1 L3
1 L2
2 L2
2 L2
2 L2
3 L2
3 L3
4 L2
4 L2
4 L2



Answer

c3 c4

1 L3
2 L2
3 L3
4 L2


I want to lookup FOR 1,2,3,4 From the column 1 and column 2 ,

but Condition 1. If (1) has L2 and L3 it should show only L3.
Condition 2. If (1) has L2 and L2 it should show only L2.

same thing for other lookup of 2,3,4.

Can anybody help me out for this .

[Paul]

Hi Ravanan, and welcome to the forum.

Rather than using VLOOKUP, try this:

=IF(SUMPRODUCT(--($A$1:$A$11=E1),--($B$1:$B$11="L3"))>=1,"L3","L2")

Where E1 holds the number 1 (E2 could hold 2, E3 has 3, E4 has 4). Or, you could replace E1 with the actual value (1,2,3 or 4).

Hope that helps!

[ravanan]

Thank you paul