Lookups

[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.

 

[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

 

[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.

 

[Sarah at DaVita]

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

 

[Pete_UK]

Glad to hear you found something that worked for you.

Pete