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


Download Workbook


6 Possible Solutions to Fix VLOOKUP #N/A Error

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.

vlookup na error

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.

Checking If the Lookup Value Exists

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.

fixing vlookup na error by checking for lookup value

Read More: [Fixed!]: VLOOKUP Function Is Returning Same Value in Excel


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.

Correcting Mistyped Lookup Value

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

fixing vlookup na error by correcting misspelling

Read More: [Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value


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.

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

fixing vlookup na error by correcting array

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

Using INDEX MATCH functions

Read More: Excel VLOOKUP Returning Column Header Instead of Value


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.

Entering the Correct Data Formatting

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.

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

fixing vlookup na error by using proper data format

Read More: [Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)


Solution 5: Removing Extra Space

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

Removing Extra Space

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

fixing vlookup na error by removing extra spaces


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.

Using Absolute Cell Reference for the Table 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.

fixing vlookup na error Using Absolute Cell Reference for the Table Array

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.

Specifying Appropriate Match Method in VLOOKUP Formula

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

fixing vlookup na error by Specifying Appropriate Match Method in VLOOKUP Formula


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.

What to Do If VLOOKUP Function Is Not Returning Correct Value

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

fixing vlookup na error using Calculation Options


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 since we, the Exceldemy team, are happy to answer your queries.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo