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.

**Table of Contents**hide

## 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 **ID**s, 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**(argument) is matched from the*lookup_value***$B$5:$D$14**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***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.*

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

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**(argument) is matched from the*lookup_value***$C$5:$D$14**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → Operations**

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

📌 ** 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 theargument that refers to the*lookup_value**Name Milly*. Following,**C5:C14**represents theargument from where the value is matched. Lastly,*lookup_array***0**is the optionalargument which indicates the*match_type***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 theargument which is the marks scored by the students. Lastly,*array***5**is theargument that indicates the row location.*row_num***Output → Operations**

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

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

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

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 theargument which refers to the text*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”**(argument) is mapped from the*lookup_value***$C$5:$D$14**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***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.

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