Lookup an array

[Guest]

Hi, I require a formula that will answer the following problem.

I have cells A1100 full of data and these are my reference cells.

I also have cells F1:H50 full of data.

What I require is a lookup formula that will lookup cells F1:H1, F2:H2 etc
compared with cells A1:C100 and if all of the 3 cells are matched then return
the corresponding value from column D.

Thanks

 

[Bob Phillips]

=INDEX($D$1:$D$100,MATCH(1,($A$1:$A$100=$F2)*($B$1:$B$100=$G2)*($C$1:$C$100=
$H2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ardus Petus]

If I were you, I would add a helper colum before each table, with formula in
A1:100 =B1&"|"&C1&"|"&D1
Insert column before second table, and fill with same formula

Then you can use a simple VLOOKUP formula to get the desires result

HTH