SOLVED
Home

Problem with VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-544885%22%20slang%3D%22en-US%22%3EProblem%20with%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544885%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all!%20I%20have%20this%20formula%20that%20makes%20certain%20that%20the%20value%20in%20column%20G%20is%20set%20to%20%22Confirmed%22%2C%20then%20if%20it%20is%2C%20I%20have%20it%20Vlookup%26nbsp%3B%20as%20so%3A%26nbsp%3B%3DIF(%24G6%3D%22Confirmed%22%2CVLOOKUP(%24B6%2C%24J%242%3A%24N%247%2C3%2C%24L%242%3A%24L%247)%2C%22N%2FA%22).%20So%2C%20here%20in%20lies%20my%20problem...%20if%20works%20for%20some%20of%20the%20categories%20in%20column%20B%2C%20but%20not%20others%2C%20as%20it%20returns%20%23value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20figure%20it%20out%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-544885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545929%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545929%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337802%22%20target%3D%22_blank%22%3E%40ntscooby%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(%24G6%3D%22Confirmed%22%2CVLOOKUP(%24B6%2C%24J%242%3A%24N%247%2C3%2C%3CSTRONG%3E%24L%242%3A%24L%247%3C%2FSTRONG%3E)%2C%22N%2FA%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Bold%20part%20needs%20to%20be%20replaced%20by%20FALSE%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(%24G6%3D%22Confirmed%22%2CVLOOKUP(%24B6%2C%24J%242%3A%24N%247%2C3%2C%3CSTRONG%3EFALSE%3C%2FSTRONG%3E)%2C%22N%2FA%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545950%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337802%22%20target%3D%22_blank%22%3E%40ntscooby%3C%2FA%3E%26nbsp%3B%20Hi%2C%20%26nbsp%3BThe%20last%20part%20of%20a%20VLOOKUP%20formula%20is%20usually%20a%20TRUE%20or%20FALSE%20value%20depending%20on%20whether%20an%20exact%20match%20is%20required%20or%20not.%20Remove%20the%20reference%20to%20%24L%242%3A%24L%247%20and%20replace%20with%200%20or%201%20depending%20on%20your%20requirements%20and%20see%20what%20happens.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549132%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3Eworked%20perfectly.%20Thanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-549137%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-549137%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20response!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-550461%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-550461%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome%3C%2FLINGO-BODY%3E
ntscooby
New Contributor

Hey all! I have this formula that makes certain that the value in column G is set to "Confirmed", then if it is, I have it Vlookup  as so: =IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,$L$2:$L$7),"N/A"). So, here in lies my problem... if works for some of the categories in column B, but not others, as it returns #value.

 

Can anyone help me figure it out?

5 Replies
Solution

Hi @ntscooby 

 

=IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,$L$2:$L$7),"N/A"

 

The Bold part needs to be replaced by FALSE

 

=IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,FALSE),"N/A"

@ntscooby  Hi,  The last part of a VLOOKUP formula is usually a TRUE or FALSE value depending on whether an exact match is required or not. Remove the reference to $L$2:$L$7 and replace with 0 or 1 depending on your requirements and see what happens.

@Wyn Hopkinsworked perfectly. Thanks so much!

Thanks for your response!
You’re welcome