**VLOOKUP** is a widely used function in Excel to extract a column of data from one sheet to another. It usually looks for a value in the dataset and extracts the data from our desired column corresponding to the lookup value. However, this function causes massive mass in the calculation. This article will describe 11 possible causes and solutions to the issue of Excel **VLOOKUP** drag down not working. If you are curious about them, download our practice workbook and follow us.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

## 11 Possible Solutions for Excel VLOOKUP Drag Down Not Working

To demonstrate the solutions, we consider a dataset of **10** employees of any organization. The dataset contains employees’ IDs, names, residency areas, number of family members, incomes, and living costs. So, we can say our dataset is in the range of cells **B5:G14**.

In this context, we will see how to fix the Excel **VLOOKUP** drag down not working issue. The generic representation of the** VLOOKUP** function is given below;

**VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])**

Here,

**lookup_value**: The value we are looking for keeping in the first column of our dataset or table.**table_array**: The table in which we look for the value.**column_index_num**: The column in the dataset or table from which we get our desired value.**range_lookup**: An optional requirement contains 2 cases,**TRUE**for the**Approximate match**which is the**default**, and**FALSE**for the**Exact match**.

### Solution 1: Change Calculation Options

Sometimes the change of calculation option of Excel causes trouble for us when we drag down a function. We have to change it for getting an accurate result. Now, if you look at our dataset, you will see it shows the same result in column **C** for all employees.

The solution to fix this issue are given below:

**📌 Steps: **

- First of all, select any cell to check the formula from
**Formula Bar**. We select cell**D6**. - Though our formula was correct, it didn’t get the exact result.

- To resolve this issue, select the
**Formula**tab. - Then, click on the
**drop-down**arrow of the**Calculation Option**and choose the option**Manual**to**Automatic**.

- You will see within a second the
**VLOOKUP**function will extract the accurate result.

Thus, we can say that our method worked perfectly and we are able to fix the issue of **VLOOKUP** drag down not working in Excel.

**Read More: [Fixed!] Excel Drag to Fill Not Working (8 Possible Solutions)**

### Solution 2: Insert Absolute Cell Reference in Lookup Array

When we write down **the VLOOKUP function** to get the data, we need to ensure the **Absolute Cell Reference** in the **table_array**. Otherwise, if the position of the data changes, the function may not provide us with an accurate result. In our file, you will see the function gives any outcome for cells **B12** and **B13**. As the position of those cells does not match with our original dataset, thus the function cannot provide us with any value.

The steps for resolving this problem are given as follows:

**📌 Steps:**

- At first, select cell
**D5**and you will see the**table_array**reference doesn’t have the**Absolute Cell Reference**sign. - Write down the following formula into cell
**D5**and ensure the**Absolute Cell Reference**in the**table_array**. If you don’t know how to add**Absolute Cell Reference**, you can add it in several ways.

`=VLOOKUP(B5,Dataset!$B$4:$G$14,3,TRUE)`

- Now, press the
**Enter**.

- After that,
**double-click**on the**Fill Handle**icon to copy the formula up to cell**D14**. - You will see the function gets the correct value for the corresponding lookup value.

At last, we can say that our formula worked effectively and we are able to fix the issue of **VLOOKUP** drag down not working in Excel.

**Read More: How to Drag Formula Horizontally with Vertical Reference in Excel**

### Solution 3: Remove Duplicate Data from Dataset

The presence of duplicate data in the dataset cause difficulties for us when we drag down the** VLOOKUP** function. It is mainly a human error. We will accidentally input any value twice. In our dataset, you can see that the **VLOOKUP** function doesn’t give the actual result for cell **B11**. Instead of **South East**, our function provides **North West and Merseyside** for cell **B11**.

The procedure to fix this cause is given below:

**📌 Steps:**

- At the beginning of this process, select the range of cell
**B5:B14**. - Now, in the
**Home**tab, select the**drop-down arrow**of the**Conditional Formatting**option from the**Styles**group. - Then, choose the
**Highlight Cell Rules > Duplicate Values**option.

- You will see the duplicate values highlighted.

- After that, input the correct value. In our dataset, we input the correct
**ID 202207**. - At last, you will see the value of cell
**D11**will change to our desired result.

Finally, we can say that our correction process worked perfectly and we are able to fix the issue of **VLOOKUP** drag down not working in Excel.

**Read More: How to Fill Down to Last Row with Data in Excel (3 Quick Methods)**

### Solution 4: Keep Data Matching with Approximate Match

Sometimes finding the exact matching of data causes trouble to get the value through **the VLOOKUP function**. We also have a similar case in our dataset in cell **D11**.

The steps to solve the issue are explained below:

**📌 Steps:**

- Select cell
**D11**to check the formula in the**Formula Bar**. - Now, change the case match type from
**TRUE**to**FALSE**. The formula will like as shown below:

`=VLOOKUP(B5,Dataset!$B$4:$G$14,3,FALSE)`

- Press
**Enter**. - You will see the function will get the value from the main dataset.

In the end, we can say that our procedure worked perfectly and we are able to fix the problem of **VLOOKUP** drag down not working in Excel.

**Read More: How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)**

### Solution 5: Eliminate Empty Cells from Dataset

Sometimes we accidentally delete any cell value from the original dataset. As a result, it causes complications for Excel to extract the value with the function. It is also a human error. You can easily find it. In this case, the function shows **0** from which we can predict that something goes wrong here. In our dataset, cell **D13** is showing such a result.

The process to fix this issue is given below:

**📌 Steps:**

- Go to the main
**Dataset**sheet from the**Sheet Name Bar**. - Now, input the deleted cell value manually. If you have a large dataset you can find your desired data by selecting the data range.
- Input the data manually by your keyboard, and press the
**Enter**.

- Then, go back into the previous sheet and you will see the problem is solved.

So, we can say that we are able to fix the **VLOOKUP** drag down not working problem in Excel.

**Read More: [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)**

### Solution 6: Type Accurate Lookup Value

Inputting an incorrect lookup cell reference, sometimes causes a mass for Excel to get the value according to our desire. In such an occurrence, **the VLOOKUP function** cannot perform its task properly. So that we can say the drag down will also not work. In our dataset, we have such a **#N/A error** in the entire range of cells **D5:D14**.

The steps for resolving this complication are given below:

**📌 Steps:**

- First of all, select cell
**D5**to check the function argument. You can click on the cell reference to see the cell selected in the spreadsheet. - You can see that instead of cell
**B5**, we choose**A5**in the function.

- Now, press the
**Backspace**button to clear the cell reference and select cell**B5**. The modified formula is shown below:

`=VLOOKUP(B5,Dataset!$B$4:$G$14,3,TRUE)`

- Press the
**Enter**.

- After that,
**double-click**on the**Fill Handle**icon to copy the new formula up to cell**D14**. - You will get all the desired values.

Thus, we can say that we are able to identify and fix the problem of **VLOOKUP** drag down not working in Excel.

### Solution 7: Store Lookup Value in the Leftmost Column

**The VLOOKUP function** cannot work properly if we don’t input the far left cell of our original dataset as the **lookup_value**. In this case, the function returns some wired value in the result like the image shown below.

The approach to fix this difficulty is described below:

**📌 Steps:**

- Select cell
**D5**and modify the**lookup_vaue**cell reference from**C5**to**B5**.

`=VLOOKUP(B5,Dataset!$B$4:$G$14,3,TRUE)`

- Then, press
**Enter**. The function will show our desired area in the cell.

- Now,
**double-click**on the**Fill Handle**icon to copy the formula up to cell**D14**. - The issue will solve and you will get value for all employees.

Lastly, we can claim that we can fix the **VLOOKUP** drag down not working problem in Excel.

### Solution 8: Insert Correct Column Index Number

The addition of a new column can cause a mass with **the VLOOKUP function**. It changes the **column_index_num**, as a result, **the VLOOKUP function** doesn’t return the desired result. Our dataset faced a similar problem and all the numbers came to **0**.

The way to solve this problem is explained below:

**📌 Steps:**

- At first, select cell
**D5**in the**Formula Bar**and input the correct**column_index_num.** - In our case, the new
**column_index_num**is**5**. The formula will like as shown below:

`=VLOOKUP(B5,$K$4:$Q$14,5,TRUE)`

- Now, press
**Enter**.

- After that,
**double-click**on the**Fill Handle**icon to copy the formula up to cell**D14**. - The hitch will solve and you will get value for all the entities.

Thus, we can claim that our formula worked precisely and we are able to fix the **VLOOKUP** drag down not working problem in Excel.

### Solution 9: Choose Correct Table Array

Inputting an incorrect **table_array** reference is another reason to get an error from **the VLOOKUP function**. In such an event, we will show a **#N/A error** in the dataset. As the function cannot perform its task properly, so we can confirm that the drag down of that will also not work. In our file, we have such similar type of **#N/A** error in the entire range of cells **D5:D14**.

The method for resolving this hitch is given below:

**📌 Steps:**

- In the starting, select cell
**D5**to check the function argument in the**Formula Bar**. - Then, write down the proper function with accurate table_array like below:

`=VLOOKUP(B5,Dataset!$B$4:$G$14,3,TRUE)`

- Now, press the
**Enter**key.

- Next,
**double-click**on the**Fill Handle**icon to copy the new formula up to cell**D14**. - You will get all tour desired values.

Finally, we can say that our error solving technique worked properly and we are able to fix the issue of **VLOOKUP** drag down not working in Excel.

### Solution 10: Set Relevant Cell Format

Sometimes a previously set cell format can create trouble for us while we import the data from one sheet to another through the** VLOOKUP** function. When we tried to get the Income value for all the employees, we got some unpredictable values in our dataset like the image. This is happen due to wrong cell formatting.

The solution to this kind of problem is shown below:

**📌 Steps:**

- First of all, go to your original datasheet. In our file, it is in sheet
**Dataset**. - Then, select any cell of that column to check the data format.
- After that, check the data types from the
**Number**group of the**Home**tab.

- Again, go to the sheet where you used the function. In our workbook, the sheet is titled
**Irrelavent Cell Format**. - Select the entire range of cells
**D5:D14**. - Now, in the
**Home**tab, from the**Number**group choose the similar data types. For us, we chose**Accounting**as the data type. - You will see all the meaningful cell values like the original dataset.

In the end, we can say that we are able to locate and fix the complication of Excel **VLOOKUP** drag down not working.

**Read More: Drag Number Increase Not Working in Excel (A Solution with Easy Steps)**

### Solution 11: Remove Invisible Dash

The trap up of invisible dashes sometimes occur obstacles to getting the cell value through the** VLOOKUP** function. To fix this issue:

- First, go to your original dataset and find out the entity in which the problem will show up.
- Then,
**delete**the existing data and manually input them again.

The problem will be solved and you will get your desired data.

## Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to fix the issue of Excel VLOOKUP drag down not working. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website **ExcelDemy** for several Excel-related problems and solutions. Keep learning new methods and keep growing!