Nested Lookup !! ?

[jase]

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

 

[Domenic]

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!

 

[N Harkawat]

=INDEX(C1:C10,MATCH(1,(A1:A10="b")*(B1:B10=3)*ISNUMBER(C1:C10),0))
array entered (ctrl+shift+enter)

The "b" and 3 above should be replaced with the actual cell reference