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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies