Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
V Lookups | Excel Discussion (Misc queries) | |||
Lookups | Excel Worksheet Functions | |||
Maybe I need help with Lookups?? | Excel Worksheet Functions | |||
Lookups | Excel Worksheet Functions | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |