Lookup value on a table??

[Pas]

I have the following table below. If I place in cell "A100" the figure -7 and
in cell "A101" the figure -975. What formula do I need to put in cell "102"
the result, in this case it would be 787.
Also on column "A" if the figure is between two (ie -960) it should use the
row with the highier value, in this case the result would still be 787.



A B C D E F
1 -10 -9 -8 -7 -6
2 -1000 790 790 788 786 784
3 -975 791 791 789 787 785
4 -950 791 791 789 787 785
5 -925 792 792 790 788 786
6 -900 793 793 791 789 787
7 -875 793 793 791 789 787
8 -850 794 794 792 790 788
9 -825 794 794 792 790 788
10 -800 795 795 793 791 789

 

[p45cal]

try:
=OFFSET($A$1,MATCH(A101,$A$1:$A$10)-1,MATCH(A100,$A$1:$F$1)-1,1,1)

 

[Pas]

Sorry, it's giving #NA.

 

[T. Valko]

Works OK for me.

--
Biff
Microsoft Excel MVP

Sorry, it's giving #NA.

 

[Pas]

I can see where I'm going wrong. I have negative values on the top line as
well. The full table would have been too big to post. The top line measures
temperature and starts at -15.