Lookups

  • Thread starter Sarah at DaVita
  • Start date
S

Sarah at DaVita

I have a table like this.
begin end answer
..05 .08 1
..09 .16 2
..17 .25 3
I have sheet with information like this
0.03
0.08
0.16
0.195
0.24
I want to look up the number in the sheet and find the range and return the
full number. So .16 would return a two and .195 would return a 3.
Can excel handle this? I could use nested if statements but then formula
gets very long.
 
P

Pete_UK

You don't actually need the end column in your table, but you will
have to define a condition for when your value is less than 0.05.

Assume your table with headings is in J1:L4, and that your data is in
column A starting with A1, then put this formula in B1:

=IF(A1<$J$2,"too small",VLOOKUP(A1,$J$2:$L$4,3))

Instead of "too small" you could have 0. Then copy this formula down
for as many values as you have in column A.

Hope this helps.

Pete
 
S

Sarah at DaVita

Thanks but that does not work. I tried a nested if statement with ands but I
cannot get enough in the formula before excel balks.
 
S

Sarah at DaVita

Discovered the match function - it works great to get me where I need to be.
Thanks.
 
Top