Top 4 Errors in VLOOKUP and How to Fix Them?

The VLOOKUP function cannot give you errors due to data mismatch. Instead, it may return the errors.

  • #N/A Error#NAME? Error#REF! Error#VALUE! Error

Let us discuss each error in detail with an example.

#1 Fixing #N/A Error in VLOOKUP

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 formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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 excelLEN ExcelThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more 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.

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 excelTRIM Function In ExcelThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces.read more. We can automatically delete the spaces by applying the VLOOKUP function and the TRIM function.

The TRIM function removes the extra unwanted space.

#2 Fixing #VALUE! Error in VLOOKUP

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.

#3 Fixing VLOOKUP #REF 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.

#4 Fixing VLOOKUP #NAME 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.

Things to Remember

  • 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.

This article is a guide to the VLOOKUP Errors in Excel. We discuss fixing the four most common errors #N/A, #VALUE! #NAME? And REF! in the VLOOKUP function, Excel example, and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Vlookup to LeftVLookup with IF FunctionMultiple Criteria of VLOOKUPIFERROR with VLOOKUP in Excel