SOLVED
Home

Need help DESPERATELY. Ready to give up ! vlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-489569%22%20slang%3D%22en-US%22%3ENeed%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489569%22%20slang%3D%22en-US%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EEnclosed%20is%20my%20spreadsheet%20to%20total%20add%20all%20feet%2C%20inches%20and%20fractions.%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThen%20convert%20them%20all%20back%20to%20Feet%2C%20Inches%20and%20Fractions%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThe%20table%20VLOOKUP%20function%20looks%20at%20the%20value%20in%20cell%20F14%20%22faction%22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThe%20data%20table%20in%20M4%20to%20N195%20is%20named%20%22RANGE%22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThis%20table%20represents%20all%20inch%20and%20fractions%20from%201%2F16%22%20to%2011%2015%2F16%22%20IN%20DECIMALS%20OF%20A%20FOOT!%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWhen%20you%20add%20or%20subtract%20a%20fraction%20that%20%3D0%20I%20get%20an%20error%20in%20cell%20F15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWhen%20you%20add%20or%20subtract%20a%20fraction%20that%20is%20%26gt%3B0%20I%20get%20an%20error%20in%20cell%20F18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EI%20have%20been%20trying%20to%20correct%20the%20error%20for%20way%20too%20long%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThis%20is%20why%20I%20am%20reaching%20out%20to%20someone%20to%20PLEASE%20assist%20me%20with%20this%20error!%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETHANK%20YOU!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-489569%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489669%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328572%22%20target%3D%22_blank%22%3E%40Pro_Street_Falcon%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20VLOOKUP%20is%20incorrect%20here%3C%2FP%3E%0A%3CPRE%3E%3DIF(FRACTION%3D0%2C%220%22%2CVLOOKUP(RANGE%2C2%2C))%3C%2FPRE%3E%0A%3CP%3Eif%20change%20the%20syntax%20on%3C%2FP%3E%0A%3CPRE%3E%3DIF(FRACTION%3D0%2C%220%22%2CVLOOKUP(FRACTION%2CRANGE%2C2))%3C%2FPRE%3E%0A%3CP%3Eit%20works%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489706%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328572%22%20target%3D%22_blank%22%3E%40Pro_Street_Falcon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20is%20a%20reason%20why%20you%20don't%20use%20COMVERT()%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489741%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489741%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20but%20i%20have%20never%20used%20that%20function.%3C%2FP%3E%3CP%3EIf%20possible%20could%20please%20give%20me%20an%20example%20as%20it%20pertains%20to%20my%20spreadsheet.%3C%2FP%3E%3CP%3EThank%20You!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489751%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20formula%2C%20I%20appreciate%20any%20help.%3C%2FP%3E%3CP%3EI%20still%20have%201%20problem%20(sorry)%3C%2FP%3E%3CP%3EThe%20vlookup%20function%20always%20picks%20a%20row%20above%20the%20stated%20lookup%20value.%3C%2FP%3E%3CP%3EIF%20if%20i%20input%20a%20fraction%20into%20column%20%3CSTRONG%3E%3CEM%3EF%3C%2FEM%3E%3C%2FSTRONG%3E%20example%2C%20input%201%2F2%22%20it%20%3CEM%3E%3CSTRONG%3Ealways%3C%2FSTRONG%3E%3C%2FEM%3E%20returns%20one%20row%20above%20as%207%2F16%22%3C%2FP%3E%3CP%3EAnd%20yes%20i%20tried%20change%20to%20FALSE%20for%20an%20exact%20match%20and%20get%20a%20%23N%2FA.%3C%2FP%3E%3CP%3ESorry%20but%20i%20have%20been%20working%20on%20this%20for%20a%20very%20long%20time%2C%20on%20%26amp%3B%20off.%3C%2FP%3E%3CP%3EI%20sure%20would%20like%20to%20put%20this%20to%20bed.%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489760%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328572%22%20target%3D%22_blank%22%3E%40Pro_Street_Falcon%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20what%20you'd%20like%20to%20receive.%20With%20aproximate%20search%20you%20have%20closest%20value%20before%20searched%20one.%20With%20exact%20search%20you%20may%20wrap%20your%20formula%20with%20IFNA%20and%20return%20any%20value%20you'd%20like%20for%20such%20case%2C%20e.g.%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(%26lt%3Byour%20formula%26gt%3B%2C%200)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489771%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489771%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328572%22%20target%3D%22_blank%22%3E%40Pro_Street_Falcon%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOops%2C%20the%20issue%20is%20another%20-%20your%20range%20is%20calculated%20and%20you%20have%20rounding%20error%2C%20thus%20no%20exact%20match.%20As%20variant%20you%20may%20correct%20the%20formula%20to%3C%2FP%3E%0A%3CPRE%3E%3DVLOOKUP(ROUND(FRACTION%2C6)%2CROUND(RANGE%2C6)%2C2)%3C%2FPRE%3E%0A%3CP%3E(see%20in%20J15)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489817%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489817%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328572%22%20target%3D%22_blank%22%3E%40Pro_Street_Falcon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3EUsing%20CONVERT()%20also%20means%20you%20can%20get%20rid%20of%20your%20helper%20table%20and%20your%20rounding%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489870%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVERY%20close%20but%20no%20cigar.%3C%2FP%3E%3CP%3ETried%20the%20example%20as%20above%20%26amp%3B%20get%20a%20%23VALUE!%20error.%3C%2FP%3E%3CP%3EThe%20previous%20formula%20works%20awesome%2C%26nbsp%3B%3DVLOOKUP(FRACTION%2CRANGE%2C2%2CTRUE)%3C%2FP%3E%3CP%3EEXCEPT%3C%2FP%3E%3CP%3EThe%20only%20error%20i%20get%20if%20my%20input%20area%20is%20completely%20BLANK.%20then%20i%20get%20a%20%3CEM%3E%3CSTRONG%3E%23N%2FA%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20only%201%20piece%20of%20data%20input%20it%20works.%20Range%20from%20C5%20to%20F12.%3C%2FP%3E%3CP%3ENot%20a%20big%20game%20changer%2C%20just%20a%20small%20annoyance.%20Otherwise%20all%20is%20correct.%3C%2FP%3E%3CP%3EI%20do%20want%20to%20THANK%20YOU%20VERY%20MUCH%20for%20you%20taking%20the%20time%20to%20help%20me%20with%20this%20problem.%3C%2FP%3E%3CP%3EI%20really%20appreciate%20it%3C%2FP%3E%3CP%3ETHANK%20YOU!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-489877%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20DESPERATELY.%20Ready%20to%20give%20up%20!%20vlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-489877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20STAND%20CORRECTED!%20Used%20the%20formula%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3DIF(FRACTION%3D0%2C%220%22%2CVLOOKUP(FRACTION%2CRANGE%2C2))%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EWORKS%20PERFECT%20!!!%3C%2FP%3E%3CP%3EFinally%20thanks%20to%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Pro_Street_Falcon
Occasional Contributor
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

@Pro_Street_Falcon ,

 

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

@Pro_Street_Falcon 

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

 

@Detlef Lewin 

Thanks but i have never used that function.

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

Thank You!

@Sergei Baklan 

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

@Pro_Street_Falcon ,

 

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)

@Pro_Street_Falcon ,

 

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)

@Pro_Street_Falcon 

See attached file.

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

 

 

@Sergei Baklan 

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!

 

Solution

@Sergei Baklan 

I STAND CORRECTED! Used the formula 

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

WORKS PERFECT !!!

Finally thanks to your help!

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies