There may be times when you need to know easily how to lookup across multiple sheets in Excel. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to lookup across multiple sheets in Excel with various methods and lots of examples.

`📁`

Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.

## Learn to Lookup Across Multiple Sheets in Excel Using These 3 Unique Methods

Let’s say we want to know which type of fruit is famous in which region of America. In this tutorial, we’ll show you 3 different ways to combine these two lists of fruits grown in different regions into a new and complete list and find the fruit famous in that region.

And here is our second dataset.

**Method 1**

**1. Using IFERROR Function**

We need to combine the information from the “First Dataset” and “Second Dataset ” worksheets to make a full list of fruits that are famous in a specific region. Here we are going to use **IFERROR**, and **VLOOKUP** functions. You can find and fix errors in a formula by using the **IFERROR **function. If a formula gives an error, **IFERROR **will return a value you choose. Otherwise, it will return the result of the formula. We use **VLOOKUP **to find things by row in a table or a range.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, we select cell
**C6**. Then we put the formula below:

**=IFERROR(VLOOKUP(B6,’First Dataset’!$B$6:$C$13,2, FALSE), IFERROR(VLOOKUP(B6,’Second Dataset’!$B$6:$C$13, 2, FALSE), “Not found”))**

- Then we press
**Enter**and drag down the formula.

- The final result will be like this.

`🔨`

Formula Breakdown

`👉`

The **B6 **gets an ID, which can be used as a search key.

`👉`

**First Dataset!$B$6:$C$13 **indicates searching operation done in the range from **B6** to **C13** in the First Dataset.

`👉`

**Second Dataset!$B$6:$C$13 **indicates searching operation done in the range from **B6** to **C13** in the Second Dataset.

`👉`

**2 **says the **Book Name** column to retrieve the book names.

`👉`

**FALSE **says exact matches while we search.

`👉`

**IFERROR(VLOOKUP(B6,’First Dataset’!$B$6:$C$13,2, FALSE), IFERROR(VLOOKUP(B6,’Second Dataset’!$B$6:$C$13, 2, FALSE), “Not found”))** returns the Fruit name with that region.

**Method 2**

**2. Lookup with INDIRECT Function**

We need to combine the information from the “First Dataset” and “Second Dataset ” worksheets to make a full list of fruits that are famous in a specific region. Here we are going to use **INDIRECT**, **VLOOKUP**. **INDIRECT **function returns the reference that a text string points to. We use **VLOOKUP **to find things by row in a table or a range. We will also use the **INDEX** and **MATCH** functions. Within a given table or range, the **INDEX **function will return either a value or a reference to a value. The **MATCH **function looks for a given item in a range of cells and returns the relative position of that item within the range.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, we select cell
**C6**. Then we put the formula below:

**=VLOOKUP($B6,INDIRECT(“‘”&INDEX($E$6:$E$7,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&$E$6:$E$7&”‘!$B6:$B21″),$B6)>0,0))&”‘!$B$6:$C$21”),2,0)**

- Then we press
**Enter**and drag down the formula.

- The final result will be like this.

`🔨`

Formula Breakdown

`👉`

The **B6 **gets an ID, which can be used as a search key.

`👉`

**Lookup_range $B6:$B21** indicates searching operation done in the range from **B6** to **C13** in the First Dataset.

`👉`

**Table_array $B$6:$C$21** indicates searching operation done in the range from **B6** to **C21** in the Second Dataset.

`👉`

**2 **says the **Book Name** column to retrieve the book names.

`👉`

**Lookup_sheets** **$F$5:$F$6 **indicates the cell address of the sheets throughout which we are going to lookup data.

`👉`

**VLOOKUP($B6,INDIRECT(“‘”&INDEX($E$6:$E$7,MATCH****(TRUE,COUNTIF(INDIR****ECT(“‘”&$E$6:$E$7&”‘!$B6:$B21″),$B6)>0,0))&”‘!$B$6:$C$21”),2,0) **returns the Fruit name with that region.

**Method 3**

**3. Implementing Nested IF Function**

We need to combine the information from the “First Dataset” and “Second Dataset ” worksheets to make a full list of fruits that are famous in a specific region. Here we are going to use **IF**, **VLOOKUP**, and **ISNA **functions. We use **VLOOKUP **to find things by row in a table or a range. The **IF **function is one of the most used functions in Excel. It lets you compare a value to what you expect in a logical way. **ISNA **returns **True **or **False **if the value is **#N/A** (value not available).

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, we select cell
**C6**. Then we put the formula below:

**=IF(ISNA(VLOOKUP($B6,’FirstDataset’!$B$6:$C$13,2,0)),VLOOKUP($B6,’Second Dataset’!$B$6:$C$13,2,0),IF(ISNA(VLOOKUP($B6,’Second Dataset’!$B$6:$C$13,2,0)),VLOOKUP($B6,’First Dataset’!$B$6:$C$13,2,0)))**

- Then we press
**Enter**and drag down the formula.

- The final result will be like this.

**🔨 Formula Breakdown**

`👉`

The **B6 **gets an ID, which can be used as a search key.

`👉`

**First Dataset!$B$6:$C$13 **indicates searching operation done in the range from **B6** to **C13** in the First Dataset.

`👉`

**Second Dataset!$B$6:$C$13 **indicates searching operation done in the range from **B6** to **C13** in the Second Dataset.

`👉`

**2 **says the **Book Name** column to retrieve the book names.

`👉`

If **ISNA(VLOOKUP($B5,Online!$B$6:$C$13,2,0))** becomes true then we pull the book name using **VLOOKUP($B6,’First Dataset’!$B$6:$C$13,2,0)).**

## How to Use XLOOKUP Across Multiple Sheets in Excel

**XLOOKUP** is very user friendly. You can look for things in a table or range by row with the **XLOOKUP **function.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, we copy the formula in new sheet. Then we select cell
**F6**. Then we put the formula below:

**=XLOOKUP(B6,DATASET!B6:B13,DATASET!C6:C13)**

- Then we press The final result will be like this.

`📄`

Important Notes

`🖊️`

In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

`🖊️`

A practice workbook is given so that you can practice yourself.

`🖊️`

All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

`🖊️`

At the side of each Excel worksheet file, there is space where they can practice.

`📝`

Takeaways from This Article

`📌`

Reader will be able to lookup across multiple sheets in Excel in various methods.

📌 They can utilize **IFERROR **Function to lookup across more than one sheet.

`📌`

Readers can use Excel Vlookup several sheets with **INDIRECT**.

`📌`

Finally, they can lookup across more than one sheet in Excel using the Nested **IF **function.

## Conclusion

That concludes today’s session. These are the methods for using Excel to match names in excel where spelling differs. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, **ExcelDen**, the best Excel solutions provider.