Categorising Vlookup #N/A error

%3CLINGO-SUB%20id%3D%22lingo-sub-1946948%22%20slang%3D%22en-US%22%3ECategorising%20Vlookup%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1946948%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20to%20know%20if%20there%20are%20any%20methods%20to%20easily%20identity%20why%20the%20Vlookup%20formula%20returns%20%23N%2FA.%20So%20far%20I%20have%20come%20across%20two%20scenarios%20where%20it%20is%20shown%20%23N%2FA.%3CBR%20%2F%3E1.%20When%20there%20is%20a%20mismatch%20%2F%20typo%20between%20the%20lookup%20value%20and%20the%20value%20in%20respective%20table%20array.%3CBR%20%2F%3E2.%20When%20the%20lookup%20value%20is%20not%20found%20in%20the%20respective%20column%20in%20table%20array.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20any%20ways%20to%20categorise%20%2F%20differentiate%20the%20%23N%2FA%20error%20between%20these%20two%20types%20in%20order%20to%20easily%20identify%20and%20correct%20the%20cases%20in%20first%20scenario%20instead%20of%20going%20through%20all%20the%20%23N%2FA%20cells.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20doing%20vlookup%20for%20a%20text%20which%20is%20formatted%20in%20%22company%20name_dd.mm.yyyy%22.%20There%20will%20be%20changes%20to%20the%20dates%20after%20the%20name%20but%20I%20want%20to%20identify%20only%20typos%20in%20the%20company%20name%20part%20and%20not%20the%20dates.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1946948%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-1947306%22%20slang%3D%22en-US%22%3ERe%3A%20Categorising%20Vlookup%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F887898%22%20target%3D%22_blank%22%3E%40Bharathgopi%3C%2FA%3E%26nbsp%3BExcel%20can't%20differentiate%20between%20a%20company%20name%20that%20was%20incorrectly%20spelled%20and%20%26nbsp%3Ba%20correctly%20spelled%20company%20name%20that%20does%20not%20exist%20in%20a%20look-up%20table.%20In%20both%20cases%2C%20the%20company%20name%20is%20not%20found%20with%20%23NA!%20as%20the%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20your%20other%20question%20as%20I%20understand%20it%20is%20how%20to%20look-up%20only%20the%20company%20names%20that%20are%20part%20of%20a%20larger%20text%20string.%20In%20your%20example%20the%20names%20and%20dates%20are%20separated%20by%20a%20hyphen.%20Then%20you%20could%20do%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-02%20at%2007.14.19.png%22%20style%3D%22width%3A%20684px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237194i4C4E78F87AD068B4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-02%20at%2007.14.19.png%22%20alt%3D%22Screenshot%202020-12-02%20at%2007.14.19.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948037%22%20slang%3D%22en-US%22%3ERe%3A%20Categorising%20Vlookup%20%23N%2FA%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20which%20you%20had%20mentioned%20doesn't%20work%20in%20my%20case%20because%20the%20table%20array%20values%20also%20contains%20the%20same%20format%20which%20is%20%22Company%20name_dd.mm.yyyy%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20a%20snip%20to%20show%20an%20example%20how%20my%20lookup%20value%20looks%20and%20how%20my%20table%20array%20values%20look%20and%20also%20the%20result%20I%20expect%20to%20get%20using%20some%20Vlookup%20formula%20or%20with%20a%20combination%20of%20vlookup%20and%20other%20formulas.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20it%20is%20feasible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20%26amp%3B%20Regards%3C%2FP%3E%3CP%3EBharath%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Excel%20query.PNG%22%20style%3D%22width%3A%20763px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237317iDBA24227E4EADABD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel%20query.PNG%22%20alt%3D%22Excel%20query.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi,

I would like to know if there are any methods to easily identity why the Vlookup formula returns #N/A. So far I have come across two scenarios where it is shown #N/A.
1. When there is a mismatch / typo between the lookup value and the value in respective table array.
2. When the lookup value is not found in the respective column in table array.

Is there any ways to categorise / differentiate the #N/A error between these two types in order to easily identify and correct the cases in first scenario instead of going through all the #N/A cells.

I am doing vlookup for a text which is formatted in "company name_dd.mm.yyyy". There will be changes to the dates after the name but I want to identify only typos in the company name part and not the dates.
3 Replies

@Bharathgopi Excel can't differentiate between a company name that was incorrectly spelled and  a correctly spelled company name that does not exist in a look-up table. In both cases, the company name is not found with #NA! as the result.

 

Now, your other question as I understand it is how to look-up only the company names that are part of a larger text string. In your example the names and dates are separated by a hyphen. Then you could do something like this:

Screenshot 2020-12-02 at 07.14.19.png

 

@Riny_van_Eekelen

 

Thank you for your reply.

 

The formula which you had mentioned doesn't work in my case because the table array values also contains the same format which is "Company name_dd.mm.yyyy". 

 

I have added a snip to show an example how my lookup value looks and how my table array values look and also the result I expect to get using some Vlookup formula or with a combination of vlookup and other formulas. 

Let me know if it is feasible.

 

Thanks & Regards

BharathExcel query.PNG

 

@Bharathgopi Sorry. Can't answer. Your picture doesn't show the formula you used. Please upload the file.