Forum Discussion
Need help DESPERATELY. Ready to give up ! vlookup
| Enclosed is my spreadsheet to total add all feet, inches and fractions. |
| Then convert them all back to Feet, Inches and Fractions |
| The table VLOOKUP function looks at the value in cell F14 "faction" |
| The data table in M4 to N195 is named "RANGE" |
| This table represents all inch and fractions from 1/16" to 11 15/16" IN DECIMALS OF A FOOT! |
| When you add or subtract a fraction that =0 I get an error in cell F15 |
| When you add or subtract a fraction that is >0 I get an error in cell F18 |
| I have been trying to correct the error for way too long |
| This is why I am reaching out to someone to PLEASE assist me with this error! |
| THANK YOU! |
I STAND CORRECTED! Used the formula
=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2)) WORKS PERFECT !!!
Finally thanks to your help!
9 Replies
Thanks but i have never used that function.
If possible could please give me an example as it pertains to my spreadsheet.
Thank You!
See attached file.
Using CONVERT() also means you can get rid of your helper table and your rounding problem.
Your VLOOKUP is incorrect here
=IF(FRACTION=0,"0",VLOOKUP(RANGE,2,))
if change the syntax on
=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2))
it works
I STAND CORRECTED! Used the formula
=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2)) WORKS PERFECT !!!
Finally thanks to your help!
Thank you for the formula, I appreciate any help.
I still have 1 problem (sorry)
The vlookup function always picks a row above the stated lookup value.
IF if i input a fraction into column F example, input 1/2" it always returns one row above as 7/16"
And yes i tried change to FALSE for an exact match and get a #N/A.
Sorry but i have been working on this for a very long time, on & off.
I sure would like to put this to bed.
Thanks again
Oops, the issue is another - your range is calculated and you have rounding error, thus no exact match. As variant you may correct the formula to
=VLOOKUP(ROUND(FRACTION,6),ROUND(RANGE,6),2)
(see in J15)
