Lookup text with multiple search criteria

[analyst]

I know how to look up data and text using vlookup and hlookup, what I'
like to know is whether there is a way of looking up data based on mor
than 1 search criteria; for example

If I wanted to look up an item in a 4 column database, I'd us
something like this:

=VLOOKUP("Apples",A210,4,false)

But that only looks for Apples. Say I wanted to lookup data based on
subcategory of Apples, e.g. colour.

If it was a number, I could use SUMPRODUCT and (assuming named range
were in use) do it like this:

=SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total))

But obviously SUMPRODUCT is no good if the data you want to return i
text.

Is there an equivalent text lookup function that will help me? Maybe a
INDEX/MATCH formula? I just can't get my head around it!!

Thanks for any help

 

[Guest]

http://www.cpearson.com/excel/lookups.htm
This site may help you.

 

[Domenic]

Is there an equivalent text lookup function that will help me? Maybe an
INDEX/MATCH formula?

Yes there is! Here's an example where we want to return the price/lb
for yellow apples:

Fruit Color Price/lb
Apple Red 0.48
Apple Yellow 0.50
Grapes White 0.60
Grapes Red 0.59

=INDEX(C2:C5,MATCH(1,(A2:A5="Apple")*(B2:B5="Yellow"),0))

which must be entered using CTRL+SHIFT+ENTER.

Hope this helps!

 

[analyst]

Brilliant! That works a treat!

Thanks

 

[Domenic]

That works a treat!

Thanks!

You're welcome!