How do I find a number on a chart?

[Brodie]

I have a ton of data that represents a chart we use. I would like to find a predetermined number on the left axis, then within that row find another number, and return a value at the top axis. Like find the 4 below, then the 99, and lastly the .21

.19 .2 .21 .22

1 24 25 26 27
2 63 64 65 66
3 78 79 80 81
4 97 98 99 100
5 8 9 10 11

Thanks in advance

Brodie

 

[Jon Peltier]

Assuming the data is in the top left of a sheet, with cell A1 blank, you can build up the formula bit by bit.

To find the value 4 in the first column:
MATCH(4,A2:A6) returns 4

To find 99 in the corresponding row:
MATCH(99,B5:E5)
MATCH(99,OFFSET(B2:E2,4))
MATCH(99,OFFSET(B2:E2,MATCH(4,A2:A6))) returns 3

To find the value in the top row:
INDEX(A2:A6,3)
INDEX(A2:A6,MATCH(99,OFFSET(B2:E2,MATCH(4,A2:A6)))) returns 0.21

 

[Brodie]

Thanks for your help. I have always used vlookups but I need more versatility to solve this one. I couldn't the "INDEX(A2:A6,MATCH(99,OFFSET(B2:E2,MATCH(4,A2:A6))))" command to work. There are too few arguments for the OFFSET portion. I also tried setting up the same chart as I gave in the example using the command "=INDEX(A2:A6,MATCH(99,OFFSET(B2:E2,MATCH(4,A2:A6),0)))" and that returns a value of 4. Any ideas as to ehy this is happening?

 

[Brodie]

I got it to work. Thanks. The command is below.

"=INDEX(B1:E1,MATCH(99,OFFSET(B2:E2,(MATCH(A10,A2:A6)-1),)))"