How should I do a multi-key search like lookup

P

Peo Sjoblom

=SUMPRODUCT(--(A2:A100="x"),--(B2:B100="y"),D2:D100)

or

=INDEX(D2:D100,MATCH(1,(A2:A100="x")*(B2:B100="y"),0))

the latter entered with ctrl + shift & enter

are two examples, it will look for 2 columns in this case where the lookup
value
are "x" and "y". You can easily adapt to more columns

=SUMPRODUCT(--(A2:A100="x"),--(B2:B100="Y"),--(C2:C100="z"),D2:D100)

and

=INDEX(D2:D100,MATCH(1,(A2:A100="x")*(B2:B100="y")*(C2:C100="z"),0))
 
B

bingy

I tried the 2nd method but failed.
I put numbers in cell and could get 0 or 1 on calling
=INDEX(((--(A1:A7=9))*(--(B1:B7=66))),1),
but get #VALUE! when trying =MATCH(1,((--(A1:A7=9))*(--(B1:B7=66))),0), why?

bingy
 
F

Frank Kabel

Hi bingy
you have to enter this formula with CTRL+SHIFT+ENTER (as Peo wrote in
his post)

Frank
 
Top