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

Get FREE Advanced Excel Exercises with Solutions!

VLOOKUP function is widely used for importing data from a worksheet or workbook. However, due to the function’s argument issue, this function will cause some trouble. Among them, returning a similar value is one. In this article, we are going to demonstrate 2 possible solutions where the VLOOKUP function is returning the same value in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

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


2 Possible Solutions for VLOOKUP Function Is Returning Same Value in Excel

To demonstrate the solutions, we consider a dataset of 10 employees of a company. Their name and the income of the first three months are in the range of cells B5:E14.

Now, if we apply the VLOOKUP function to import the income amount for January, it returns us the correct value. Whereas, when we use the Fill Handle icon to get the value of February and March, it will return the same value as January. The problem you can also notice in the image shown below:

Returning the same value in each cell

Its solution procedure is described below.

📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


1. Inputting Accurate Column Index Number

Inputting the wrong column index number is one of the common reasons to return the same value for every column. Because when we use the Fill Handle icon to copy the formula of the VLOOKUP function, it does not change the value of the col_index_num. As a result, it provides us with the same result. The steps to fix this problem are given below:

📌 Steps:

  • First of all, select cell D5.
  • Now, change the col_index_num from 2 to 3.
  • Then, press Enter.

Inputting Accurate Column Index Number to Fix the Problem of Getting Same Value from VLOOKUP Function

  • After that, drag the Fill Handle icon to copy the formula upto cell D14.

  • You will notice that this time the function will extract the accurate value.
  • Similarly, modify the formula for column March.

Inputting Accurate Column Index Number

  • Your problem will be resolved.

Thus, we can say that the col_index_num correcting process works effectively, and we are able to solve the issue when the VLOOKUP function is returning the same value.

Read More: Excel VLOOKUP Returning Column Header Instead of Value


Similar Readings


2. Combining INDEX and MATCH Functions

The combination of INDEX and MATCH functions is another way to avoid the problems created by the VLOOKUP function. This combination is much handy, and it can remove all types of errors that we face if we use the VLOOKUP function. The steps of this method are explained below step-by-step:

📌 Steps:

  • First, select cell C5.
  • Now, write down the following formula in the cell.
  • Press Enter.

Applying the INDEX and MATCH function

  • Then, drag the Fill Handle icon to copy the formula upto cell E5.

Use the Fill Handle icon to copy formula row-wise

  • After that, select the range of cells C5:E5 and drag the Fill Handle icon to copy the formula upto cell E14.

  • You will see that all the cells have imported the correct value from the Dataset sheet.

Combination of INDEX and MATCH Functions to Fix VLOOKUP Function Returning the Same Value

Finally, we can say that our formula works successfully, and we can resolve the problem of getting the same value by the VLOOKUP function by the combination of the INDEX and MATCH functions.

🔎 Breakdown of the Formula

We are breaking down the formula for cell C5.

👉 MATCH(Dataset!$B5,Dataset!$B$5:$B$14,0): The MATCH function will find the position of the value which is in cell B5. Here, the function will return 1.

👉 MATCH(Dataset!C$4,Dataset!$B$4:$E$4,0): Here, the MATCH function will check the relative position of the value which is in cell C5. Here, the function will return 2.

👉 INDEX(Dataset!$B$4:$E$14,MATCH(Dataset!$B5,Dataset!$B$5:$B$14,0)+1,MATCH(Dataset! C$4,Dataset!$B$4:$E$4,0)): Using the value of both MATCH function the INDEX function will extract the income value from the Dataset sheet. For this cell, the value returns  $5,925.

Read More:[Fixed!] Excel VLOOKUP Function Not Calculating Automatically


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 trouble that is the VLOOKUP function returning the same value in Excel. Please share any further queries or recommendations with us in the comments section below if you have any other 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo