The VLOOKUP is Not Working Between Sheets – 8 Easy Solutions

This is the Source sheet from which you will extract data.

Solution 1- Entering the Correct Lookup Value, the Worksheet Name, and the Column Index Number

In a new worksheet (Type), extract the names corresponding to the Student ID numbers in the Source worksheet.

• The name of the worksheet is misspellt: Sourc and the #N/A error is displayed.

• Enter the following formula with the correct sheet name.

• Using a lookup value that does not match the values in the lookup array can also return a the #N/A error. Here, 1001, instead of 11001.

• Enter the correct lookup value. This is the formula.

If the correct column index number isn’t provided,  the #VALUE! error is displayed.
Here, column number was 2, but we 0 was the input.

• Use the following formula.

Solution 2 – Creating a Dataset with the Lookup Value in the First Column

In the Source dataset, the Name column is in the first position. If you look up any value except for this column, the VLOOKUP will not work between the sheets.

In the Lookup table serial datasheet, the students’ names were extracted depending on their ids.

• Enter the following formula.

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

• Place Student ID in the first column, so that it contains the lookup value.

Enter the following formula.

This is the output.

Solution 3 – Applying the Absolute Referencing

The serial was changed in the Student ID column.

Using the formula like in the previous methods, the two last cells display errors and the reference of the main table array changed from B3:D13 to B12:D22.

• Use the following formula with absolute referencing.

You will be able to extract the correct values from the Source worksheet.

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

Solution 4 – Cleaning Hidden Spaces in the Lookup Values

Sometimes there are hidden spaces or characters in the lookup array which can cause VLOOKUP not to work between sheets.

• Go to the Source sheet, add an extra column (Helper), and enter the following formula.

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

The numbers will be formatted as text. You must change their format to number.

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

In the Format Cells dialog box:

• Choose the Category as Number and click OK.

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

• Choose the main column Student ID, right-click it, and select Paste Values.

• You can delete the Helper column.

• Enter the following formula to get the results.

Solution 5 – Using a Lookup Value Greater Than the Smallest Value in the Array for an Approximate Match

Use an approximate match instead of an exact match. The lookup value can not be smaller than the smallest value present in the table array. A new ID: 11000 was inserted.

After using the following formula, the #N/A error is displayed.

• Use the ID 11011 instead of 11000, and the error will be removed.

The VLOOKUP will work correctly between sheets and return an approximate value.

Solution 6 – Arranging Values in the Lookup Array in Ascending Order

Using an approximate match, you have to arrange the table array in ascending order. The order of the dataset in the Source worksheet was changed into descending order.

Because of the descending order, the formula returns the #N/A error.

Change the order of the dataset in the Source worksheet:

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

The dataset will be displayed in ascending order.

• Go to the worksheet from which you want to extract data.

This is the output.

Solution 7 – Checking If Numbers are Formatted as Numbers and Not Texts

In the Source worksheet, the numbers are stored as text. The VLOOKUP will not work between sheets.

• Use the following formula to extract names.

It will return an error.

• Go to the Source worksheet, select the cell, click the error symbol, and choose Convert to Number.

This is the output.

• Go to the sheet from which you want to extract values, and enter the following formula.

This is the output.

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

Solution 8 – Adjust the Formula after Inserting or Deleting A Column

• Enter the following formula to get the results below.

Delete the Name column in the Source worksheet.

the #REF! error will be displayed due to the change of the index column number.

• Use the following formula.

This is the output.

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

Sometimes you may need to extract data from another workbook. The main dataset is in the Source sheet of the New workbook.

By entering Now instead of New (the workbook name) errors are displayed.

• Enter the following formula with the correct name.

This is the output.

Download Workbook

Related Articles

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

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