Double VLOOKUP
 

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.

Double VLOOKUP
 

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


"CCrew2000" wrote in message
...
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.

Double VLOOKUP
 

Try this instead.

=SUMPRODUCT((A1:A5="Pear")*(B1:B5="Green")*(C1:C5) )

Mike

"CCrew2000" wrote:

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.

Double VLOOKUP
 

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


"CCrew2000" wrote:

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.

Double VLOOKUP
 

=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)

"CCrew2000" wrote in message
...
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.