Multiple criteria VLOOKUP

[Jason]

Hi,

I have a seven column dataset (A1:G2000) and I want to lookup a value
based on 3 criteria: one for each of the 1st three columns, so where the
value in column A = X, column B = Y, column C = Z,
with XYZ being three criteria defined by three other cells.

Can anyone point me in the right direction. I'm wanting to figure out
how to do it without creating a helper column where columns A,B and C
are concatenated, and using the concatenating field as a lookup key.

Any help greatly appreciated,

Thanks....Jason

 

[muddan madhu]

Array formula

=INDEX(D110,MATCH("x"&"y"&"z",A1:A10&B1:B10&C1:C10,0),0) ( use ctrl
+ shift + enter )

or

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10="z")*(D110))

 

[Jason]

Thanks for the suggestions. I figured it out in the end, using a
similar approach to your SUMPRODUCT i.e.

=INDIRECT("D"&SUMPRODUCT(--(A1:A2000="X"),--(B1:B2000="Y"),--(C1:C2000="Z"),
ROW(D12000))

Thanks again, the INDEX/MATCH approach is a useful approach,

Regards,

Jason