Double VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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
 
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
 
=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)
 
Back
Top