How to Lookup Across Multiple Sheets in Excel (3 Easy Methods)

Usually, it is quite common not to have all the necessary information within the same worksheet. But, to make a complete database, we need to look up other sheets in Excel for the necessary data retrieval. As a result, to handle this type of hassles, Microsoft Excel has come up with a versatile function called VLOOKUP; which enables users to lookup across multiple sheets in Excel. In this article, using the VLOOKUP function, we will teach you 3 suitable methods that you can use to lookup across multiple sheets in Excel with ease.

How to Lookup Across Multiple Sheets in Excel: 3 Suitable Methods

For instance, a bookshop sells books both online and in stores. However, they have two booklists featuring books available for online sale and books available for sale in the store.

In this tutorial, we will teach you how can you combine these two lists of books and make a new complete booklist using 3 distinct methods.

1. Lookup Across Multiple Sheets in Excel with IFERROR Function

First of all, to make a complete booklist that contains available books for sale both online and in stores, we need to combine the information both from the “Store” and “Online” worksheets. Here, we will use the IFERROR function with the VLOOKUP function in order to complete the operation. Hence, read through the steps below.

🔗 Steps:

❶ Initially, select cell C5 ▶ to store the formula result.

❷ Then, type the following formula:

❸ Next, press the ENTER button.

❹ After that, drag the Fill Handle icon to the end of the Book Name column.

💡 Similarly, to make the Author column complete, write the formula in cell D5.

💡 Finally, hit the Enter button and utilize the AutoFill tool to the entire column.

2. Combine INDIRECT, INDEX and COUNTIF Functions 

Furthermore, we can lookup across multiple sheets using the INDIRECT function instead of the IFERROR function. However, we must bear in mind that though the INDIRECT function provides a lot more flexibility while retrieving data across multiple sheets, the syntax of the INDIRECT function is more complicated. So, be careful while using the INDIRECT function along with the VLOOKUP function. Here, we have also combined COUNTIF, INDEX, and MATCH functions. Hence, without any further discussion let’s go straight into the steps:

🔗 Steps:

❶ Firstly, select cell C5 ▶ to store the formula result.

❷ Secondly, insert the following formula.

❸ Thirdly, press the ENTER button.

❹ Fourthly, drag the Fill Handle icon to the end of the Book Name column.

💡 Similarly, to make the Author column complete, use the formula mentioned below in cell D5.

💡 Lastly, utilize the AutoFill tool and the final output will appear as in the image below.

3. Combine IF and ISNA Functions to Lookup Across Multiple Sheets

Last but not least, there’s another way to lookup across multiple sheets in Excel. However, we can do it with the usage of the IF function along with the ISNA and VLOOKUP functions. Additionally, if you have a few datasheets to pull the data off then you can use this method with ease otherwise, you are not recommended to use it. Because it gets much more complicated as the number of sheets increases Anyways, follow the below steps to learn how the formula works.

🔗 Steps:

❶ Initially, select cell C5 ▶ to store the formula result.

❷ Then, insert the formula below.

❸ Next, press the ENTER button.

❹ Afterwards, drag the Fill Handle icon to the end of the Book Name column.

💡 Similarly, to make the Author column complete, use the formula.

💡 Finally, you will get your desired output.

Things to Remember

• Initially, the lookup value should always be in the first column within the table array.
• Then, use Ctrl + Shift + Enter together to complete the array formulas.
• Next, use carefully the syntax of the functions.
• After that, insert the data ranges carefully into the formulas.

Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.

Conclusion

To wrap up, we have discussed 3 distinct methods to lookup across multiple sheets in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

<< Go Back to Lookup | Formula List | Learn Excel