[Fixed!] Excel VLOOKUP Returning #N/A Error (6 Possible Solutions)

Undoubtedly, the VLOOKUP function is one of the most useful functions in Excel. However, like everything else in this world, it has its downsides too. Have you faced a situation where the VLOOKUP function returns a #N/A error? Do not despair! Because you’re not alone. Luckily, in the following tutorial, we’ll demonstrate 6 fixes for the VLOOKUP #N/A error. In addition, we’ll also discuss fixes for selecting the incorrect match method and choosing the appropriate Calculation Options in Excel.

Excel VLOOKUP Returning #N/A Error: 6 Solutions

Firstly, let’s be clear about the #N/A error! It stands for Not Available, which refers to the fact that the VLOOKUP function did not find a match for the search.
Let’s consider the List of Employees and Departments dataset shown in the B4:D14 cells, which depicts a list of employee IDs, their Names, and the Department where they work. So, without further delay, let’s take a glance at each problem and its solution with appropriate illustrations.

Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

Solution 1: Checking If the Lookup Value Exists

First and foremost, let’s address a common cause of the VLOOKUP #N/A error in Excel. Simply put, the lookup value may not be present in the lookup array. As a result, the function returns an #N/A error.

Therefore, simply correcting the value will return the results.

📌 Steps:

At the very beginning, enter the correct ID number in the C18 cell >> type in the formula given below.

=VLOOKUP(C18,$B$5:$D$14,2,FALSE)

In this case, the C18 cell refers to the ID number 1002.

Formula Breakdown:

VLOOKUP(C18,$B$5:$D$14,2,FALSE) → looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here, C18 ( lookup_value argument) is matched from the $B$5:$D$14 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → Hans

📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.

Solution 2: Correcting Mistyped Lookup Value

In truth, another simple error that drives people crazy is a simple typo in the lookup value, which causes the function to output the #N/A error, as evident in the image below. Here, the Name Milly has been misspelled as Milli.

Luckily, the fix is just as simple, so follow along.

📌 Steps:

In the first place, enter the correct Name in the C18 cell >> then, insert the expression in the D18 cell.

=VLOOKUP(C18,$C$5:$D$14,2,FALSE)

For instance, the C18 cell indicates the Name Milly.

Formula Breakdown:

VLOOKUP(C18,$C$5:$D$14,2,FALSE) → here, C18 ( lookup_value argument) is matched from the $C$5:$D$14 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → Operations

Solution 3: Referencing the Leftmost Column

For one thing, the VLOOKUP function cannot retrieve data from its left side, which means the lookup column must be the leftmost column, otherwise, the function returns the #N/A error as shown in the picture below. In this example, the lookup value Milly is not located in the leftmost column.

📌 Steps:

Initially, navigate to the D18 cell >> insert the equation into the Formula Bar.

=VLOOKUP(C18,$C$5:$D$14,2,FALSE)

Now, this should display the correct result, which is the Department of Operations.

Alternatively, you can also utilize the INDEX and MATCH functions without having to worry about the lookup column being at the left.

In a similar style, enter the formula given below into the D18 cell.

=INDEX(D5:D14,MATCH(C19,C5:C14,0))

Specifically, the C19 cell points to the Name Milly.

Formula Breakdown:

MATCH(C19,C5:C14,0) → returns the relative position of an item in an array matching the given value. Here, C19 is the lookup_value argument that refers to the Name Milly. Following, C5:C14 represents the lookup_array argument from where the value is matched. Lastly, 0 is the optional match_type argument which indicates the Exact match criteria.
- Output → 5
INDEX(D5:D14,MATCH(C19,C5:C14,0)) → becomes
- =INDEX(D5:D14,5) → returns a value at the intersection of a row and column in a given range. In this expression, the D5:D14 is the array argument which is the marks scored by the students. Lastly, 5 is the row_num argument that indicates the row location.
- Output → Operations

Solution 4: Entering the Correct Data Formatting

Oftentimes, the VLOOKUP #N/A Error occurs because the formatting of the lookup value has been modified while importing it or by mistake. Typically, a leading apostrophe causes the data to be interpreted as text, as seen in the screenshot below.

Now, it’s simple and easy, just follow along.

📌 Steps:

To begin with, move to the C18 cell >> press the F2 key to enter and remove the apostrophe comma.

Afterward, in the D18 cell insert the equation given below >> This returns the correct output, Jules.

=VLOOKUP(C18,$B$5:$D$14,2,FALSE)

In this above expression, the C18 cell refers to the ID number 1004.

Solution 5: Removing Extra Space

Conversely, the VLOOKUP formula does not function correctly if the lookup value contains extra spaces.

On this occasion, we’ll employ the TRIM function to remove any additional spaces within the lookup value which should resolve the VLOOKUP #N/A Error.

📌 Steps:

First, enter the D18 cell >> next, type in the following expression.

=VLOOKUP(TRIM(C18),$C$5:$D$14,2,FALSE)

Formula Breakdown:

TRIM(C18) → removes all but single spaces from a text. Here, the C18 cell is the text argument which refers to the text “Milly ” and the function gets rid of excess spaces after the text.
- Output → “Milly”
VLOOKUP(TRIM(C18),$C$5:$D$14,2,FALSE) → becomes
- VLOOKUP(“Milly”,$C$5:$D$14,2,FALSE) → Here, “Milly” ( lookup_value argument) is mapped from the $C$5:$D$14 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → Operations

Solution 6: Using Absolute Cell Reference for the Table Array

Last but not least, discarding the Absolute Cell Reference can also lead to the VLOOKUP #N/A Error. Now, this is because copying the formula with the Fill Handle tool shifts the cells of the lookup array, thus, the function cannot match the lookup value from the given array.

📌 Steps:

To start with, proceed to the D18 cell >> apply the formula shown below.

=VLOOKUP(C19,$C$5:$D$14,2,FALSE)

For example, the C19 cell represents the Name Roman.

📃 Note: Press the F4 key on your keyboard to lock in the $C$5:$D$14 cell references.

All said and done, the world we live in is far from perfect! Though the methods above are all possible fixes for the VLOOKUP #N/A Error, if the problem persists as the last option, you can contact Microsoft Support. Here, you can find many Excel experts who will provide solutions for your particular issues.

Specifying Appropriate Match Method in VLOOKUP Formula

Additionally, specifying the wrong match method in the VLOOKUP function produces erroneous output even if the data exists. Here, the TRUE argument refers to the Approximate Match condition which matches the nearest value and hence returns the incorrect result. In the following section, we’ll discuss how to troubleshoot this issue.

📌 Steps:

First and foremost, go to the D18 cell >> enter the following equation.

=VLOOKUP(C18,$B$5:$D$14,2,FALSE)

In this scenario, the FALSE argument indicates the Exact Match criteria in the VLOOKUP function.

What to Do If VLOOKUP Function Is Not Returning Correct Value

Finally, you can check if Excel’s Calculation Options is set to Manual which can cause the VLOOKUP function to yield the same output when copying the formula into the cells below. Normally, this feature prevents the slowdown of the computer by preventing unnecessary calculations. Admittedly, not reverting to the default Automatic option can cause issues, as shown in the screenshot below.

📌 Steps:

Initially, navigate to the Formulas tab >> click the Calculation Options drop-down >> check the Automatic option.

Immediately, the returns the correct output as evident in the picture below.

Download Practice Workbook

Fixed VLOOKUP #N/A Error.xlsx

Conclusion

In essence, this article describes 6 quick and easy fixes for the VLOOKUP #N/A Error. Now, we hope you found it helpful, and do inform us in the comment section about your experience.