[Fixed!] Excel VLOOKUP Drag Down Not Working (11 Possible Solutions)

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.

Excel VLOOKUP Drag Down Not Working: 11 Possible Solutions

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 VLOOKUP function drag down not working issue. The generic representation of the VLOOKUP function is given below;

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 solutions 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 VLOOKUP Function Not Calculating Automatically

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

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

Solution 3: Remove Duplicate Data from Dataset

The presence of duplicate data in the dataset causes 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.

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 be as shown below:

• 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: [Solved]: Excel VLOOKUP Not Working with Numbers

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.

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:

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

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

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

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

Solution 11: Remove Invisible Dash

The trap of invisible dashes sometimes causes 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.

Download Practice Workbook

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

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.

Keep learning new methods and keep growing!

Related Articles

• [Fixed!] Excel VLOOKUP Not Working Due to Format
• [Fixed!] VLOOKUP Not Working Between Sheets
• VLOOKUP Not Picking up Table Array in Another Spreadsheet

<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel