Double VLOOKUP


G

Guest

I have a database that is pretty big. In a nutshell, the database looks like:

A B C
Apple Red 12
Apple Yellow 5
Apple Green 7
Pear Green 6
Pear Blue 2

I'd like the vlookup to pull the data located in the 3rd column, first by
Apple (column A) and then by color (column B). So if I'm looking for Red
apples, I'd get the answer of 12. If I was looking for Green pears, I'd get
6.
 
Ad

Advertisements

P

PCLIVE

One way:
=SUMPRODUCT(--(A1:A5="Apple"),--(B1:B5="Red"),C1:C5)

If referring to cells that contain the lookup values. G1 = Apples H1=
Red
=SUMPRODUCT(--(A1:A5=G1),--(B1:B5=H1),C1:C5)

HTH,
Paul
 
G

Guest

try:

=SUMPRODUCT(--(A1:A5="apple"),--(B1:B5="red"),C1:C5)

Better to put "apple" and "red" in cells:

=SUMPRODUCT(--(A1:A5=H1),--(B1:B5=H2),C1:C5)

Assumption is pairs (apple/red) are unique
 
Ad

Advertisements

B

Bob Phillips

=INDEX(C:C,MATCH("Apple"&"Red",A1:A10&B1:B10,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top