Lookup an array
 

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

I have cells A1:D100 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

Lookup an array
 

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

"Chris_t_2k5" wrote in message
...
Hi, I require a formula that will answer the following problem.

I have cells A1:D100 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

Lookup an array
 

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
--
AP

"Chris_t_2k5" a écrit dans le message
de ...
Hi, I require a formula that will answer the following problem.

I have cells A1:D100 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