Vlookup with different criteria

[phiero21]

Hi,

I want to do a vlookup with 2 different criterias. I am able to achieve the result by either using a helper column or by using array formulae. Is there any way, this can be done without either of the two (without additional column or array formulae)

E.g. Here is my data set:

A 1 Ash
B 2 Bob
C 3 Cat

My Conditions are
B 2 (in 2 different cells).

I am looking for "Bob" as the output

 

[JackDanIce]

Try:

=IF(MATCH(E1,$A$1:$A$3)=MATCH(F1,$B$1:$B$3),VLOOKUP(E1,$A$1:$C$3,3,0),"No Match")

 

[MrTeeny]

You could always consider writing a custom VBA function too

 

[Marcílio_Lobão]

phieiro21, Good afternoon.

You can also use another formula if you wish.

=INDIRECT("C" & SUMPRODUCT((A1:A10=D1)*(B1:B10=E1)*ROW(C1:C10)),1)

Is that what you want?
Hope it helps.

 

[jitendra]

Its working only for unique record, If anything repeat is there then this formula is not working, for example down the given range I am adding B 5 dog and updating the range then not working.

 

[jitendra]

Its working only for unique record, If anything repeat is there then this formula is not working, for example down the given range I am adding B 5 dogand updating the range then not working.

 

[phiero21]

Try:

=IF(MATCH(E1,$A$1:$A$3)=MATCH(F1,$B$1:$B$3),VLOOKUP(E1,$A$1:$C$3,3,0),"No Match")

Thanks, works pefectly

 

[phiero21]

phieiro21, Good afternoon.

You can also use another formula if you wish.

=INDIRECT("C" & SUMPRODUCT((A1:A10=D1)*(B1:B10=E1)*ROW(C1:C10)),1)

Is that what you want?
Hope it helps.

Thanks. I like how you did this with Indirect. That's something I thought of but couldn't quite get it right.

 

[phiero21]

Thanks, works pefectly

Now I understand what Jitendra is saying. The formulae is not working if the data set contains duplicates in Column A.

However, the solution provided by Marcílio_Lobão is still working fine. Thanks for taking time though. DO let me know if your solution can be modified to include duplicate values as well.