Lookup text with multiple search criteria

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",A2:D10,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

--
Message posted from http://www.ExcelForum.com

0
6/16/2004 10:43:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
443 Views

Similar Articles

[PageSpeed] 39

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

"analyst >" wrote:

> I know how to look up data and text using vlookup and hlookup, what I'd
> like to know is whether there is a way of looking up data based on more
> than 1 search criteria; for example
> 
> If I wanted to look up an item in a 4 column database, I'd use
> something like this:
> 
> =VLOOKUP("Apples",A2:D10,4,false)
> 
> But that only looks for Apples. Say I wanted to lookup data based on a
> subcategory of Apples, e.g. colour.
> 
> If it was a number, I could use SUMPRODUCT and (assuming named ranges
> 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 is
> text.
> 
> Is there an equivalent text lookup function that will help me? Maybe an
> INDEX/MATCH formula? I just can't get my head around it!!
> 
> Thanks for any help.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
Crunched (2)
6/16/2004 5:42:01 PM
In article <analyst.17xw5o@excelforum-nospam.com>,
 analyst <<analyst.17xw5o@excelforum-nospam.com>> wrote:

> 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!
0
domenic22 (716)
6/16/2004 7:27:13 PM
Brilliant! That works a treat!

Thanks

--
Message posted from http://www.ExcelForum.com

0
6/17/2004 10:31:22 AM
In article <analyst.17zqa8@excelforum-nospam.com>,
 analyst <<analyst.17zqa8@excelforum-nospam.com>> wrote:

> That works a treat!
> 
> Thanks!

You're welcome!
0
domenic22 (716)
6/17/2004 12:38:53 PM
Reply:

Similar Artilces: