Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download the Practice Workbook

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


3 Suitable Methods to Lookup Across Multiple Sheets in Excel

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.

excel lookup across multiple sheets dataset

excel lookup across multiple sheets dataset

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:

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found"))

Lookup Across Multiple Sheets in Excel with IFERROR Function

Next, press the ENTER button.

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

Formula Breakdown

📌 Syntax: IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "Not found"))

  • B5 picks up an ID, which will work as a search key.
  • Store!$B$5:$D$9 searching operation is conducted within the range from B5 to D9 in the Store worksheet.
  • Online!$B$5:$D$9 searching operation is conducted within the range from B5 to D9 in the Online worksheet.
  • 2 indicates the Book Name column to retrieve the book names.
  • FALSE this argument is used for exact matches while searching.
  • =IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found")) ▶ returns the book name with ID no. 96.

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

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,3, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 3, FALSE), "Not found"))

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

Read More: Lookup and Return Multiple Values Concatenated into One Cell in Excel


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.

=VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$F$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$F$6&"'!$B5:$B9"),$B5)>0,0))&"'!$B$5:$D$9"),2,0)

Combine INDIRECT, INDEX-MATCH and COUNTIF Functions to lookup across multiple sheets in Excel

Thirdly, press the ENTER button.

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

Formula Breakdown

📌 Syntax: VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(TRUE, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!lookup_range"), lookup_value)>0), 0)) & "'!table_array"), col_index_num, FALSE)

  • Lookup_value $B5 the search keyword based on which we conduct the search.
  • Lookup_sheets $F$5:$F$6 the cell address of the list of sheets throughout which we generally lookup data.
  • Lookup_range $B5:$B9 the range within which our lookup value resides.
  • Table_array $B$5:$D$9 the range of the whole data table.
  • Column_index_number 2 the column number from where we pull our desired data off.

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

=VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$F$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$F$6&"'!$B5:$B9"),$B5)>0,0))&"'!$B$5:$D$9"),3,0)

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

Read More: VBA INDEX MATCH Based on Multiple Criteria in Excel (3 Methods)


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.

=IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,2,0)),VLOOKUP($B5,Online!$B$5:$D$9,2,0),IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,2,0)),VLOOKUP($B5,Store!$B$5:$D$9,2,0)))

Combine IF and ISNA Functions to Lookup Across Multiple Sheets in Excel

Next, press the ENTER button.

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

Formula Breakdown

📌 Syntax: IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_number,0)),value_if_true,value_if_false)

  • Lookup_value $B5 the search keyword based on which we conduct the search.
  • Table_array $B$5:$D$9 the range of the whole data table.
  • Column_index_number 2 the column number from where we pull our desired data off.
  • ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) searches for the cross-matching of the ID (referred by $B5) within the range $B$5:$D$9.
  • IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) If ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) is true then corresponding book name is pulled off using VLOOKUP($B5,Online!$B$5:$D$9,3,0).
  • If the part IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) becomes false then we enter into IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)).
  • IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)) If ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)) becomes true then we pull the book name using VLOOKUP($B5,Store!$B$5:$D$9,3,0)).

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

=IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0),IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)))

💡 Finally, you will get your desired output.

Read More: VLOOKUP with Multiple Criteria Including Date Range in Excel (2 Ways)


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.

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.

For more information like this, visit Exceldemy.com.


Related Readings

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo