How should I do a multi-key search like lookup

  • Thread starter Thread starter bingy
  • Start date Start date
B

bingy

I could do a single key search using lookup like functions,but how could I
do when the key is composed of multi-column valus?
Thanks for your answer.
(e-mail address removed)
 
=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))
 
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
 
Hi bingy
you have to enter this formula with CTRL+SHIFT+ENTER (as Peo wrote in
his post)

Frank
 
Back
Top