Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup "greater than or equal to" in lookup array | New Users to Excel | |||
Lookup array? | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |