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

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.

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

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

• Type the correct lookup value like the following formula.

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.

• Use the correct column index number like the following formula.

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.

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.

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.

Now, try again by typing the following formula.

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.

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.

• To fix this issue, use the following formula with absolute referencing.

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.

• Go to the Source sheet, add an extra column named Helper, and type the following formula here.

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

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.

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

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.

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

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

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.

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.

• Try to use the following formula for extracting names.

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.

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.

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

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.

• Type the following formula to get the results below.

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.

• To solve this issue use the following formula.

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.

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.

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

• [Fixed!] Excel VLOOKUP Returning #N/A Error (6 Possible Solutions)
• [Fixed!] Excel VLOOKUP Not Working Due to Format (2 Solutions)
• [Fixed!] Excel VLOOKUP Function Not Calculating Automatically