SOLVED
Home

Vlookup doesn't work for inexplicable reason

%3CLINGO-SUB%20id%3D%22lingo-sub-449779%22%20slang%3D%22en-US%22%3EVlookup%20doesn't%20work%20for%20inexplicable%20reason%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449779%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20a%20simple%20vlooukup%2C%20and%20in%20some%20cases%20although%20the%20lookup%20up%20value%20is%20in%20the%20first%20column%20in%20the%20table%20array%2C%20it%20can't%20find%20it.%20When%20i%20search%20manually%20for%20the%20searched%20value%20in%20the%20first%20column%20and%20comper%20whether%20it%20is%20equal%20to%20the%20lookup%20value%20it%20says%20true.%20So%20it%20supposed%20to%20find%20it%2C%20however%20it%20says%20%22%23N%2FA%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-449779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449834%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20doesn't%20work%20for%20inexplicable%20reason%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320587%22%20target%3D%22_blank%22%3E%40Domonkos%3C%2FA%3E%26nbsp%3B%2C%20I%20guess%20that's%20rounding%20issue.%20If%20select%20your%20column%20C%20(and%20after%20that%20J)%2C%20and%20Data-%26gt%3BText%20to%20Columns%20with%20Date%20and%20YMD%20on%20the%20third%20step%20of%20the%20wizard%2C%20that%20conversion%20rounds%20you%20date%2Ftime%20to%20seconds%20and%20VLOOKUP%20works.%20Please%20check%20in%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20%23N%2FA%20still%20exists%2C%20but%20it%20looks%20like%20really%20no%20matches.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-451026%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20doesn't%20work%20for%20inexplicable%20reason%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-451026%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%3BThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Domonkos
New Contributor

I'm using a simple vlooukup, and in some cases although the lookup up value is in the first column in the table array, it can't find it. When i search manually for the searched value in the first column and comper whether it is equal to the lookup value it says true. So it supposed to find it, however it says "#N/A". 

2 Replies
Solution

@Domonkos , I guess that's rounding issue. If select your column C (and after that J), and Data->Text to Columns with Date and YMD on the third step of the wizard, that conversion rounds you date/time to seconds and VLOOKUP works. Please check in attached.

 

Some #N/A still exists, but it looks like really no matches.