Return to Excel Formulas List

XLOOKUP – Multiple Sheets at Once – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to perform a XLOOKUP on multiple sheets in Excel. If your version of Excel does not support XLOOKUP (or you are using Google Sheets), read how to use the VLOOKUP instead.

xlookup multiple sheets Main

The XLOOKUP Function can only perform one lookup per set of data. If we want to perform a lookup among multiple sets of data that are stored in different sheets, we can nest another XLOOKUP in the 4th argument (i.e., if_not_found) of the XLOOKUP Function.

Nested XLOOKUP: If not Found

The XLOOKUP Function (or other lookup formulas) returns the #N/A error if it can’t find a match, and we can use its 4th argument to replace the error with a customized value.

Instead of a customized value, we’ll nest another XLOOKUP to perform another lookup from another sheet if the first lookup can’t find a match in the first sheet.

 

XLOOKUP – 2 Sheets at Once

xlookup multiple sheets 01

Let’s breakdown and visualize the formula:

 

XLOOKUP Function

Here’s the XLOOKUP for the 1st sheet if the 4th argument is left empty:

xlookup multiple sheets 02

Note: The XLOOKUP Function requires at least three arguments: lookup value, lookup array and return array. By default, the XLOOKUP Function finds an exact match from the top of the lookup array going down (i.e., top-down). Once it finds a match, it returns the corresponding value from the return array. Otherwise, it returns an error.

Here’s the XLOOKUP for the 2nd Sheet:

xlookup multiple sheets03

 

If not Found Argument

The 4th argument of the XLOOKUP Function and the IFNA Function work the same way. They check if a value is the #N/A Error, and if it’s true, they will return the customized value that was set.

The result of the XLOOKUP for the 2nd sheet (e.g., Department B) are used as replacement values for the #N/A Errors from the lookup for the 1st sheet (e.g., Department A).

xlookup multiple sheets 04

Combining all these concepts results to our original formula:

 

XLOOKUP – More than 2 Sheets at Once

To add more sheets, we just need to nest another XLOOKUP to the last XLOOKUP in the formula. Here’s the formula for 3 sheets:

xlookup multiple sheets 05