lookup based on 2 criteria

[SteveC]

Column A Column B Column AU
Apples text1 22
Apples text2 3
Apples text24 1
Bannanas text11 29

Cell AW2 = "Apples"
Cell AY2 = "1"

I would like a formula in Cell AX2 to return "text24"

what is that formula? thanks

I tried it myself based on numerous examples on this discussion group, but
just couldn't do it, many thanks...

SteveC

 

[Peo Sjoblom]

One way

=INDEX(B2:B1000,MATCH(1,(A2:A1000=AW2)*(C2:C100=AY2),0))

This is an array formula and needs to be entered with ctrl + shift & enter

Note that if you have an excel version previous to 2007 you would need
to specify the cell range .e.g. A2:A1000, you can't use A:A

--


Regards,


Peo Sjoblom