How should I do a multi-key search like lookup

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

 

[Peo Sjoblom]

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

or

=INDEX(D2100,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"),D2100)

and

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

 

[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

 

[Frank Kabel]

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

Frank

 

[bingy]

sincere thanks to Peo & Frank
bingy

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

Frank