Array lookup

J

j0rg3a1b3rt0

Hi, this is my situation. I have a table that I would like to find in the top
row the value given two situation, well let me give you an example:

30 45 60 75
Apple 4 3 2 1
Orange 5 2 3 4
Banana 2 4 3 1

The thing is, if I use Orange and 2 as default, I want to be able to obtain
45. Which formula I need to use?
 
D

David Billigmeier

Assume your "Apple" is in A2 and "30" is in B1:

=INDEX($A$1:$E$1,MATCH(2,OFFSET($A$2:$E$2,MATCH("Orange",$A$2:$A$4,0)-1,0),0))
 
A

Alok

Here is one of doing it

=INDEX(B1:E1,1,MATCH(A6,OFFSET(B2:E4,MATCH(A5,A2:A4,0)-1,,1),0))

This assumes that your grid is lying in A1 to E4 and A5 is where you enter
Orange etc and A6 is where you enter 2 etc.
 
Top