Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Nested Lookup !! ?
I've tried nested lookups and just get NA# : I'm sure its's not that
complicated though. There is a table with three columns like the below: a 1 1.1 b 2 1.2 b 3 1.3 c 4 1.4 d 5 1.5 e 6 1.6 f 7 1.7 In a seperate sheet I have one cell with b in it and another with 3 in it : how can I create a formula in another cell, that uses these two cells, to return 1.3 from the table ? Any help greatly appreciated Jason |
#2
|
|||
|
|||
Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(C1:C7,MATCH(1,(A1:A7=E1)*(B1:B7=F1),0)) ....where E1 contains your first criterion, such as 'b', and F1 contains your second criteria, such as 3. Hope this helps! In article om, "jase" wrote: I've tried nested lookups and just get NA# : I'm sure its's not that complicated though. There is a table with three columns like the below: a 1 1.1 b 2 1.2 b 3 1.3 c 4 1.4 d 5 1.5 e 6 1.6 f 7 1.7 In a seperate sheet I have one cell with b in it and another with 3 in it : how can I create a formula in another cell, that uses these two cells, to return 1.3 from the table ? Any help greatly appreciated Jason |
#3
|
|||
|
|||
=INDEX(C1:C10,MATCH(1,(A1:A10="b")*(B1:B10=3)*ISNU MBER(C1:C10),0))
array entered (ctrl+shift+enter) The "b" and 3 above should be replaced with the actual cell reference "jase" wrote in message ps.com... I've tried nested lookups and just get NA# : I'm sure its's not that complicated though. There is a table with three columns like the below: a 1 1.1 b 2 1.2 b 3 1.3 c 4 1.4 d 5 1.5 e 6 1.6 f 7 1.7 In a seperate sheet I have one cell with b in it and another with 3 in it : how can I create a formula in another cell, that uses these two cells, to return 1.3 from the table ? Any help greatly appreciated Jason |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |