Tanzim
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.
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:
- 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.
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:
- 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(INDIRECT(“‘”&$E$6:$E$7&”‘!$B6:$B21″),$B6)>0,0))&”‘!$B$6:$C$21”),2,0) returns the Fruit name with that region.
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:
- 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:
- 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.
