Fixing VLOOKUP Errors

VLOOKUP errors, such as "#N/A", "#REF!", and "#VALUE!", can hinder accurate data analysis and decision-making in Microsoft Excel. These Excel VLOOKUP errors often indicate issues with data consistency, formatting discrepancies, or incorrect function arguments. It is crucial to address these errors promptly to ensure the reliability and integrity of the data.

By fixing VLOOKUP errors, users can enhance the accuracy of their analyses, improve the quality of reports and presentations, and make decisions based on reliable data. Additionally, resolving these errors helps maintain the credibility of the information and enhances trust in the Excel spreadsheets and reports generated for various purposes.

VLOOKUP, a widely used function in Microsoft Excel, facilitates searching for a value in the leftmost column of a table and returning a value in the same row from a specified column. Despite its usefulness, VLOOKUP is susceptible to several common errors that users may encounter while working with data.

One prevalent error is the "#N/A" error, which signifies that VLOOKUP could not find a matching value in the leftmost column of the table. This error commonly occurs when the lookup value is misspelled, not present in the table, or formatted differently from the data in the lookup column. To resolve this error, users should ensure that the lookup value precisely matches the data in the lookup column and that there are no extraneous spaces or formatting discrepancies.

Another common issue is the "#REF!" error, which occurs when the specified column index number in VLOOKUP exceeds the number of columns in the table array. This error may also occur if the table array reference is incorrect or if rows or columns have been deleted or inserted, causing the cell references to become invalid. To fix this error, users should verify that the column index number and table array reference are accurate and adjust them as needed.

Additionally, users may encounter the "#VALUE!" error, which indicates that the input arguments in the VLOOKUP function are of the wrong data type or format. This error can occur if the lookup value is a number stored as text, if the table array reference is non-numeric, or if the column index number is not a valid numeric value. To resolve this error, users should ensure that all input arguments are of the correct data type and format, converting them if necessary.

Let us understand all these common VLOOKUP errors through this article in detail.

This error usually comes due to one of the many reasons. For example, the #N/A error means "Not Available." It is the result of the VLOOKUP formula if the formula cannot find the required value.

Before fixing this problem, we need to know why it is giving an error as #N/A. This error is due to data entry mistakes, approximate match criteria, wrong table references, wrong column reference number, data not in vertical form, etc.

We have a sales report table in Table 1. In Table 2, we have applied the VLOOKUP formula and tried to extract the values from Table 1.

In cells F4 and F9, we got errors as #N/A.That is because cell E4 looks exactly the value in cell A4. But still, we got an error a #N/A. You must be thinking why VLOOKUP has returned the result as #N/A. Nothing to worry about. Follow the below steps to rectify the error.

• Step 1: We must apply the LEN excel formula and find how many characters are in cells A4 and E4.

In cell C4, we have applied the LEN function to check how many characters are in cell A4. Similarly, we have used the LEN function in cell D4 to find how many characters are in cell E4.

In the A4 cell, we have 11 characters. But in cell E4, we have 12 characters. So one extra character is in cell E4 compared to cell A4.

By looking at the outset, both look similar to each other. However, there is one extra character. It must be a trailing space.

We can edit cell E4 and delete the space. If we delete this extra space, we will get the result.

But that is not the appropriate way to solve the issue.

• Step - 2: We can remove trailing spaces using the TRIM function in excel. We can automatically delete the spaces by applying the VLOOKUP function and the TRIM function.

The TRIM function removes the extra unwanted space.

This error is due to missing any of the parameters in the function. For example, let us look at the below table.

The VLOOKUP function starts with the LOOKUP value, the table range, column index number, and match type. If we look at the above image, the formula parameters are not in perfect order. In the place of the LOOKUP value, there is a table range. In the table range, we have column index numbers and so on.

We need to mention the formula correctly to remove this error.

This error is due to the wrong reference number. When applying or mentioning the column index number, we must reveal the exact column number from which column we are looking at the required result. If we state the column index number out of the selection range, this will return #REF! Error.

The LOOKUP value is perfect. Also, the table range is excellent. But the column index number is not perfect here. Therefore, we selected the table range from A3 to B8, which means we have chosen only two columns.

In the column index number, we have mentioned 3, which is out of range of the table range, so the VLOOKUP returns the #REF! Error result.

We must mention the correct column index number to rectify this error.

The VLOOKUP #NAME? Error displays due to the mentioned wrong formula. In my personal experience, we usually type CLOOKUP instead of VLOOKUP.

There is no formula called CLOOKUP in Excel, so returned the values as #NAME? Error type.

Solution: The solution is straightforward. We must check the formula's spelling.

• The  #N/A error may occur due to data mismatch.
• The #NAME? Error may appear due to the wrong formula type.
• The #REF! Error may display due to a wrong column index number.
• The #VALUE! Error is due to a missing or incorrect parameter supply.