function to vlookup and put between?

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi I have a simple table as follow

85 -3
65 -2
55 -1
50 0
45 1
35 2
15 3

I would like to have a formula or function that would allow me to assign a
value based on this table. For example if cell A2 = 47 then that falls
between 45 and 50 on the table and would get a value of 0. if cell A3 = 9
then it would be less than 15 and get a value of 3 (I am rounding down).
Does that make sense? Can someone help?
 
Assuming that G2:H8 contains the lookup table, and A2 contains the
lookup value, try...

=INDEX(H2:H8,MATCH(A2,G2:G8,-1))
 
Back
Top