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

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. 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. So using the VLOOKUP function, we will teach you 3 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 Methods to Lookup Across Multiple Sheets in Excel

Suppose, a book shop sells books both online and in stores. They have two booklists featuring books available for online sale and books available for sale in the store.

excel lookup across multiple sheets dataset sheet 1

 

excel lookup across multiple sheets dataset sheet 2

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

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.

joining two sheets to make another sheet Now follow the steps below to learn how to lookup:

🔗 Steps:

First of all, select cell C5 to store the formula result.

After that, 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"))
within the cell.

Then press the ENTER button.

Lookup Across Multiple Sheets in Excel with IFERROR

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

That’s it.

💡 To make the Author column complete, use the formula

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,3, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 3, FALSE), "Not found"))
within cell D5 and repeat steps 1 to 4.

  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.

2. Excel Vlookup Multiple Sheets with INDIRECT

We can lookup across multiple sheets using the INDIRECT function instead of the IFERROR function. But 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. So, without any further discussion let’s go straight into the steps:

🔗 Steps:

First of all, select cell C5 to store the formula result.

After that, type the 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)
within the cell.

Then press the ENTER button.

Excel Vlookup Multiple Sheets with INDIRECT

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

That’s it.

💡 To make the Author column complete, use the 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"),3,0)
within the cell D5 and repeat steps 1 to 4.

  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.

3. Lookup Across Multiple Sheets in Excel Using Nested IF Function

There’s another way to lookup across multiple sheets in Excel. Which is the usage of the Nested IF function along with ISNA and VLOOKUP function.

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 complicated as the number of sheets increases.

Anyways, follow the below steps to learn how the formula works. Here you go:

🔗 Steps:

First of all, select cell C5 to store the formula result.

After that, type the formula

=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)))
within the cell.

Then press the ENTER button.

Lookup Across Multiple Sheets in Excel Using Nested IF Function

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

That’s it.

💡 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)))
within the cell D5 and repeat steps 1 to 4.

␥  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)).

Things to Remember

📌 The lookup value should always be in the first column within the table array.

📌 Use Ctrl + Shift + Enter together to complete the array formulas.

📌 Be careful about the syntax of the functions.

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


Related Readings

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo