Vlookup not working why…?
There are various reasons why Vlookup will not work properly; the main reasons could be formatting issues, workbook extensions issues, incorrect application of formula and many more, let’s have a closer look to understand better.
1) Lookup only from left to right
Even though Vlookup has been so popular because of its usage and unique options, it has got its own limitations, among those this is one of the critical one. Vlookup works well with data from left to right look-up but it will not perform look-ups from right to left. When we actually face this kind of challenges we will generally do not look for best suitable solutions, instead we will add same data to the left side and use Vlookup function to complete our report, its true many excel users will do the same.
Tip: Use Match & Index formula to lookup from right to left and left to right instead of shifting your data.
2) Formatting related errors
Most of the #N/A errors are related to number formatting issues (some times it also means data not found), we often see the numbers will be stored as a text when we pull data from well known ERP’s (specially project numbers), when we use that data to do Vlookup it will show errors like #N/A with a green color tip on the top left corner (when we place our mouse curser it will show critical symbol)
Tip: If we have a green color tip in your lookup value then click on that cell and select ‘Convert to number’ option from the critical symbol dropdown. Alternatively we can also use text to columnsoption to convert the same.
3) Extension related errors
These are little tricky ones; these kinds of issues are mainly shows up while performing Vlookup from one workbook to another workbook, mainly due to different file extensions. To be precise if we are doing Vlookup from Book1.xlsx to Book2.xlsb then these kind of errors would appear, the tricky part is, we will not see any indication regarding this error, instead it will show #N/A in all the cells.
Tip: While doing Vlookup from two different workbooks make sure both the workbooks are saved in a same extension either in .xlsx or in .xlsb.
4) Blank space related errors
Often times we struggle to rectify these kinds of errors because these errors are mainly appear due to unwanted blank spaces in your data. To be precise there would be some blank spaces in your data which you cannot see but it will not allow you to do Vlookup, the reason is pretty simple, excel cannot ignore blank spaces without any instructions from the user.
Tip: Use TRIM formula to get rid of unwanted space from your data before doing Vlookup or you can use combination of TRIM and VLOOKUP functions if you are good at using multiple formulas.