Lookups
 

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.

Lookups
 

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

On Sep 2, 11:02*pm, Sarah at DaVita .
(donotspam) wrote:
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.

Lookups
 

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.

"Pete_UK" wrote:

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

On Sep 2, 11:02 pm, Sarah at DaVita .
(donotspam) wrote:
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.

Lookups
 

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

"Pete_UK" wrote:

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

On Sep 2, 11:02 pm, Sarah at DaVita .
(donotspam) wrote:
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.

Lookups
 

Glad to hear you found something that worked for you.

Pete

On Sep 3, 1:39*am, Sarah at DaVita .
(donotspam) wrote:
Discovered the match function - it works great to get me where I need to be. *
Thanks.



"Pete_UK" wrote:
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

On Sep 2, 11:02 pm, Sarah at DaVita .
(donotspam) wrote:
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.- Hide quoted text -

- Show quoted text -