VLOOKUP is a prevalent and useful ‘lookup’ function in MS Excel because it finds relevant and required data from Excel’s Table. Probably this is the reason that you use the function. But imagine a situation wherein you are working on an important business document and an incidence strikes, following which the VLOOKUP function that you are using starts rendering the #N/A (Not Available) error. Undoubtedly, this is quite worrying, troublesome, and unwanted situation for you.

Well, at such a point in time it becomes vital and need of the hour to fix the #N/A error in the VLOOKUP function of MS Excel. Nonetheless, it is necessary to first recognize the different possible causes and after that employ the methods to fix the value not available error. Read on to know these thoroughly.

Causes of #N/A Error in Excel’s VLOOKUP function

The VLOOKUP function in Excel stops working and renders #N/A error message due to several reasons and the common ones include:

• VLOOKUP function fails to find the ‘Exact Match’
• Limitation of VLOOKUP function that it cannot look towards its left
• Instances when the Lookup_value is smaller than the smallest value in the table array
• Instances when the Lookup_value that is a floating point number is ‘large’
• Cases when Lookup Column is not arranged in an ascending order

VLOOKUP function #N/A error: Causes & Resolutions

This section is a storehouse of the different causes and respective resolutions of the #N/A error in Excel’s widely used VLOOKUP function. Go through these here:

Cause 1: VLOOKUP function fails to find the Exact Match

In Excel workbooks when the range_lookup argument is FALSE and the VLOOKUP function is not able to search the ‘exact match’, though the relevant data is present in it, the #N/A error occurs.

Resolution: Remove non-printing characters and hidden spaces in the referenced cells of the Excel file. Further, ensure that the cells follow the right data type. For example, setup cells with Numbers, not ‘Text’. In case of failure, resort to CLEAN and TRIM functions responsible for cleaning data in Excel cells.

Cause 2: Limitation of VLOOKUP function that it cannot look towards its left

Excel’s VLOOKUP function cannot look towards its left (VLOOKUP’s limitation). This means it can look for ‘values’ only in the first or the leftmost column of the Table_array. If the Lookup_value is not present in the first Column, #N/A error arises in Excel worksheets.

Resolutions:

• Set the VLOOKUP function to always find the correct Column.
• If it is not possible to fix the error by adjusting the VLOOKUP function to refer to the correct Column, move the Columns.

Note – Moving the Columns may not be feasible in case Excel spreadsheets are complicated or large or both. Plus, the values in Excel cell are a result of other calculations. There may be several other logical causes for you not being able to move the Columns. In case the #N/A error is due to logical reasons, use the MATCH and INDEX functions as both these functions can look up for a value in a Column irrespective of the location of the Lookup_value in the Lookup Table.

Cause 3: Instances when the Lookup_value is smaller than the smallest value in the table_array

The TRUE function in Excel file search for the rough value in the table_array and returns the closest value that is lesser than the Lookup_value. If the argument of range_lookup is assigned to TRUE and the value in Lookup is less than the minimum value in the array, #N/A error occurs.

Resolutions:

• Change and correct the Lookup_value as per requirement.
• Use MATCH or INDEX function instead of the error-causing ‘VLOOKUP’ function. By using MATCH or INDEX function, it becomes feasible to look for values ‘more than’, ‘less than’, or ‘equal to’ the Lookup_value. Execute this solution if it is not possible or you fail to modify and consequently correct the value in Lookup.

Note – This is the same solution suggested for #N/A error that occurs if the Lookup_value is not available in the first column of the table_array.

Cause 4: Instances when the Lookup_value that is a floating point number is ‘large’

Excel stores time values in the form of floating-point numbers. However, it cannot store floating-point numbers having large values. If there exist time values or large decimal numbers in cells, #N/A error occurs.

Note – Floating-point numbers are numbers placed after the decimal in a Number.

Resolutions: The advice is to round-off the floating-point numbers of Lookup_value to five decimal places using the ROUND function for the VLOOKUP function to work properly.

Cause 5: Cases when Lookup Column is not arranged in an ascending order

If the argument or value of Range_lookup is set to TRUE and one of the Lookup Column’s is not organized from A to Z in an ascending order, #N/A error arises.

Note – It is not essential to sort or organize Lookup Column if Range_lookup is FALSE.

Resolutions:

• Set the value or argument of Range_lookup (Range_lookup argument) to FALSE so that the VLOOKUP function finds an ‘exact match.’
• In the Table that is unorganized or unsorted, employ the Excel’s INDEX or MATCH function.

Conclusion

Different resolutions cater to #N/A error in Excel documents depending on the causes. If the causes are not recognized correctly, it may be difficult to employ the correct solution. Plus, all these solutions being manual the concerned personnel would require to have an in-depth knowledge of Excel and its features. This in turn also increases the complexity and the problem intensifies. As an alternative, use Microsoft Excel File Repair Software that is a useful software to address #N/A error while repairing the Excel (XLS or XLSX) files. The added advantage is that it addresses the error regardless of the cause of the error. Further, embedded with multiple features, the software provides multiple advantages. All-in-all, a user-friendly, effective, and fast software to fix Excel issues including VLOOKUP value not available error!