3 Methods to Lookup Across Multiple Sheets in Excel

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

And here is our second dataset.

Dataset 2

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”))

excel lookup across multiple sheets Using IFERROR Function

  • Then we press Enter and drag down the formula.

excel lookup across multiple sheets with dragging down formula

  • The final result will be like this.

Using IFERROR Function

🔨  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)

excel lookup across multiple sheets and put down formula

  • Then we press Enter and drag down the formula.

Dragging down formula

  • The final result will be like this.

excel lookup across multiple sheets by looking up with INDIRECT Function

🔨  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(INDIRECT(“‘”&$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.

Dragging down formula

  • The final result will be like this.

excel lookup across multiple sheets Implementing Nested IF function

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

new dataset of excel lookup across multiple sheets

⬇️⬇️ 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)

excel lookup across multiple sheets with XLOOKUP

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

Using XLOOKUP Across Multiple Sheets to excel lookup across multiple sheets


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

(Visited 35 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo