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


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.

Changing Calculation Option to fix Excel VLOOKUP Drag Down

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.

Changing Calculation Option to fix Excel VLOOKUP Drag Down

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

Changing Calculation Option to fix Excel VLOOKUP Drag Down

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.

Insert Absolute Cell Reference to fix VLOOKUP Drag Down Not Working

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

Insert Absolute Cell Reference to fix VLOOKUP Drag Down Not Working

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.

Remove Duplicate Data to fix VLOOKUP Drag Down Not Working

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.

Remove Duplicate Data to fix VLOOKUP Drag Down Not Working

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

Remove Duplicate Data to fix VLOOKUP Drag Down Not Working

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.

Keep Data Matching Case in Approximate Match to fix VLOOKUP Drag Down

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.

Keep Data Matching Case in Approximate Match to fix VLOOKUP Drag Down

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.

Eliminate Empty Cells from Data Table to fix VLOOKUP Drag Down Not Working

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

Eliminate Empty Cells from Data Table to fix VLOOKUP Drag Down Not Working

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.

Fix Wrong Lookup Cell Reference to fix VLOOKUP Drag Down

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

Fix Wrong Lookup Cell Reference to fix VLOOKUP Drag Down

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

Fix Wrong Lookup Cell Reference to fix VLOOKUP Drag Down

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.

Use the Function for Most Left Cell of Data Table to fix VLOOKUP Drag Down

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

Use the Function for Most Left Cell of Data Table to fix VLOOKUP Drag Down

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.

Insert Correct 'column_index_num' to fix VLOOKUP Drag Down

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

Insert Correct 'column_index_num' to fix VLOOKUP Drag Down

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.

Input Correct Data Array

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

Input Correct Data Array

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.

Set Relevant Cell Format with Main Dataset to fix VLOOKUP Drag Down

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

Set Relevant Cell Format with Main Dataset to fix VLOOKUP Drag Down

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!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo