[Fixed!] VLOOKUP Not Working Between Sheets (8 Easy Fixes)

If VLOOKUP is not working between sheets of your Excel file, then you can go through this article to solve this issue. The VLOOKUP function is so much useful for finding data from another sheet, but due to some negligence, it can stop working and give us errors.
So, let’s start with the main article to know more about the solutions.


Download Workbook


8 Possible Solutions When VLOOKUP Is Not Working Between Sheets

Here, we have the following Source sheet from which we will extract our needed data. By using this dataset, we will show different common mistakes while extracting data from another worksheet. Also, the fixing ways will be shown here.

VLOOKUP not working between sheets


Fix-1: Typing Correct Lookup Value, Worksheet Name, and Column Index Number

In a worksheet named Type, we will extract the names corresponding to the Student ID numbers from the Source worksheet.

typing correctly to avoid VLOOKUP not working between sheets problem

  • Be careful about typing the name of the worksheet.

Here, we have written the name of the worksheet as Sourc and as a result, it is giving us the #N/A error.

  • Type the following formula with the correct sheet name.
=VLOOKUP(B4,Source!$B$3:$C$13,2,FALSE)

  • For using a lookup value that does not match with the values in the lookup array can also give us the #N/A error like the following. For example, we have written 1001 instead of 11001.

typing correctly

  • Type the correct lookup value like the following formula.
=VLOOKUP(11001,Source!$B$3:$D$13,2,FALSE)

Finally, if you don’t give the correct column index number as input, then it may cause the #VALUE! error like the following figure.
Here, the needed column number was 2 but we gave 0 as input.

value error

  • Use the correct column index number like the following formula.
=VLOOKUP(11001,Source!$B$3:$D$13,2,FALSE)

solving the problem of VLOOKUP Not working properly

Read More: Excel VLOOKUP Returning Column Header Instead of Value


Fix-2: Forming a Dataset with Lookup Value at First Column

Here, in the Source dataset, the Name column is in the first position. If we try to look up any value except for this column then VLOOKUP will not work between the sheets.

VLOOKUP is not working properly due to column serial

In the Lookup table serial datasheet, we will extract the students’ names depending on their ids and so, it will create a problem because of the position of the lookup array in the main dataset.

  • Here, we have entered the following formula.
=VLOOKUP(B4,Source!B3:D13,1,FALSE)

But the output is showing an error because the value in cell B4 is not in the first column of the Source datasheet.

  • Interchange the Student ID and Name columns with each other so, the first column will contain the lookup value.

interchange columns

Now, try again by typing the following formula.

=VLOOKUP(B4,Source!B3:D13,2,FALSE)

In this way, you will get the proper results.

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


Fix-3: Applying Absolute Referencing

While extracting the names of the corresponding student ids, we will show the result of not using absolute referencing. To show the effect visibly, we have changed the serial of the ids in the Student ID column.

applying absolute referencing to solve VLOOKUP not working between sheets problem

Then, we used the formula like the previous methods.
But notice that the last two cells are giving errors and the reference of the main table array has been changed from B3:D13 to B12:D22.

error

  • To fix this issue, use the following formula with absolute referencing.
=VLOOKUP(B4,Source!$B$3:$D$13,2,FALSE)

Then, you will be able to extract the correct values from the Source worksheet.


Fix-4: Cleaning Hidden Spaces in Lookup Values

Sometimes there are hidden spaces or characters in the lookup array which can cause VLOOKUP to not work between sheets. In the Extra space sheet, we will extract the names corresponding to their ids after clearing spaces in the source sheet.

Cleaning extra spaces to avoid VLOOKUP not working between sheets

  • Go to the Source sheet, add an extra column named Helper, and type the following formula here.
=TRIM(B4)

The TRIM function will clear out extra spaces in the ids of the Student ID column.

TRIM

After clearing spaces the numbers will be formatted as text, so we will change their format to number.

  • Select the range of the Helper column and press CTRL+1.

After that, the Format Cells dialog box will open up.

  • Choose the Category as Number and press OK.

  • Select the range now and press CTRL+C to copy them.

copy

  • Now, choose the main column Student ID, right-click to get various options, and select Paste Values.

  • You can delete the Helper column now.

  • Later, try out the following formula to get the results.
=VLOOKUP(B4,Source!$B$3:$D$13,2,FALSE)

VLOOKUP not working between sheets problem solved

Read More: [Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value


Fix-5: Using a Lookup Value Greater Than the Smallest Value in Array for Approximate Match

Here, we will use an approximate match instead of an exact match. But in this case, the lookup value can not be smaller than the smallest value present in the table array. To show the effect of using a lookup value smaller than the smallest value of the table array, we have inserted a new id 11000 here.

Maintaining lookup value limit to avoid VLOOKUP not working between sheets

After using the following formula we are getting the #N/A error due to using beyond the limit of the lookup value.

=VLOOKUP(B4,Source!$B$3:$D$13,2,TRUE)

  • To solve this issue, use the id 11011 instead of 11000, and then the error will be removed.

In this way, VLOOKUP is working correctly between sheets and giving an approximate value.


Fix-6: Arranging Values in Lookup Array in Ascending Order

Again, if you are using an approximate match, then you have to arrange the table array in ascending order. To show the effect of using descending order, we have changed the order of the dataset in the Source worksheet into descending order.

arranging values in ascending order to avoid the issue of VLOOKUP not working between sheets

Due to using descending order, after using the formula we are getting the #N/A error.

To solve this issue, we will change the order of the dataset in the Source worksheet now.

  • Go to the Home tab >> Editing group >> Sort & Filter dropdown >> Sort Smallest to Largest.

sort

In this way, the dataset will be ordained into ascending order.

  • Now return to the second worksheet where you were extracting data.

You will get the correct results now as we have fixed the issue of VLOOKUP not working between these sheets.


Fix-7: Checking If Numbers are Formatted as Numbers Not Texts

In the Source worksheet here, the numbers are stored as texts, not in numbers. Due to this problem, VLOOKUP will not work between sheets.

VLOOKUP not working between sheets due to numbers stored as texts

  • Try to use the following formula for extracting names.
=VLOOKUP(B4,Source!$B$3:$D$13,2,FALSE)

It will return an error due to the wrong format of the numbers in the Source worksheet.

  • To fix this issue, go to the Source worksheet, select the cell, click on the error symbol, and then choose Convert to Number.

convert to number

In this way, it will return the number to its original number format.

  • Now move to the sheet where you will be extracting values, and type the following formula.
=VLOOKUP(B4,Source!$B$3:$D$13,2,FALSE)

In this way, we have solved the issue of VLOOKUP not working between sheets.

fixing number format solved the issue of VLOOKUP not working between sheets problem

Read More: [Solved]: Excel VLOOKUP Not Working with Numbers


Fix-8: Adjust Formula after Inserting or Deleting Any Column

Here, we will extract the marks from the Source sheet, and show the effect of inserting or deleting any column.

VLOOKUP not working between two sheets due to adding or deleting a column

  • Type the following formula to get the results below.
=VLOOKUP(B4,Source!$B$3:$D$13,3,FALSE)

Then, we deleted the Name column from the Source worksheet. You can also insert a new column between them.

Then, return back to your working sheet.
But you will get the #REF! error here due to the change of the index column number now.

REF error

  • To solve this issue use the following formula.
=VLOOKUP(B4,Source!$B$3:$C$13,2,FALSE)

As the position of the Marks column has been changed in the main sheet, according to this position we have changed the index number to 2.


How to Fix When VLOOKUP Is Not Working Between Workbooks in Excel?

Sometimes you may need to extract data from another workbook. Here, we have our main dataset in a datasheet named Source of the New workbook.

VLOOKUP not working between workbooks

Then, we returned to the Workbook sheet of another workbook where we need to extract our data.
Unfortunately, we entered Now instead of New which was the workbook name, and as a result, we are getting errors.

  • Type the following formula with the correct workbook and worksheet name.
=VLOOKUP(B4,[New.xlsx]Source!$B$3:$D$16,3,FALSE)

In this way, we have extracted the marks of the students from another workbook.


Conclusion

In this article, we tried to show the ways to fix the problem of VLOOKUP not working between sheets. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo