SOLVED

# Need help DESPERATELY. Ready to give up ! vlookup

Highlighted
Occasional Contributor

# 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!
9 Replies
Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

`=IF(FRACTION=0,"0",VLOOKUP(RANGE,2,))`

if change the syntax on

`=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2))`

it works

Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

Is there is a reason why you don't use COMVERT()?

Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

Thanks but i have never used that function.

If possible could please give me an example as it pertains to my spreadsheet.

Thank You!

Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

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

Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

It depends on what you'd like to receive. With aproximate search you have closest value before searched one. With exact search you may wrap your formula with IFNA and return any value you'd like for such case, e.g.

`=IFNA(<your formula>, 0)`
Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

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)

Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

See attached file.

Using CONVERT() also means you can get rid of your helper table and your rounding problem.

Highlighted

# Re: Need help DESPERATELY. Ready to give up ! vlookup

VERY close but no cigar.

Tried the example as above & get a #VALUE! error.

The previous formula works awesome, =VLOOKUP(FRACTION,RANGE,2,TRUE)

EXCEPT

The only error i get if my input area is completely BLANK. then i get a #N/A

If there is only 1 piece of data input it works. Range from C5 to F12.

Not a big game changer, just a small annoyance. Otherwise all is correct.

I do want to THANK YOU VERY MUCH for you taking the time to help me with this problem.

I really appreciate it

THANK YOU!

Highlighted
Best Response confirmed by Pro_Street_Falcon (Occasional Contributor)
Solution

# Re: Need help DESPERATELY. Ready to give up ! vlookup

I STAND CORRECTED! Used the formula

 =IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2))

WORKS PERFECT !!!