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.

## When VLOOKUP Is Not Working Between Sheets: 8 Possible Solutions

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 with

**the VLOOKUP function**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.

`=VLOOKUP(B4,Source!$B$3:$C$13,2,FALSE)`

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

`=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.

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

`=VLOOKUP(11001,Source!$B$3:$D$13,2,FALSE)`

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

`=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.

Now, try again by typing the following formula.

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

In this way, you will get the proper results.

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

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

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

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

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

`=TRIM(B4)`

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

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

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

`=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.

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.

`=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**.

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.

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

`=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.

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

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.

**Download 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 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**

Hi

Thanks a lot for this detailed explanation.

However, it keeps giving #N/A also after checking everything!

Dear Ahmad,

Thanks for your query.

In the VLOOKUP function #N/A error occurs when you type the wrong lookup value or worksheet name. So, check if you have given the lookup value and worksheet name properly.

Again, VLOOKUP can only look up values to the right of the lookup value. Be careful about this.

If you need further help, please mention details about your problem.

Regards

Mahfuza Anika Era

ExcelDemy