XLOOKUP – Multiple Sheets at Once – Excel & Google Sheets

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.

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 4^th 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 4^th 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(B3,'Dept. A'!$B$3:$B$7,'Dept. A'!$C$3:$C$7,XLOOKUP(B3,'Dept. B'!$B$3:$B$7,'Dept. B'!$C$3:$C$7))

Let’s breakdown and visualize the formula:

 

XLOOKUP Function

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

=XLOOKUP(B3,'Dept. A'!$B$3:$B$7,'Dept. A'!$C$3:$C$7)

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 2^nd Sheet:

=XLOOKUP(B3,'Dept. B'!$B$3:$B$7,'Dept. B'!$C$3:$C$7)

 

If not Found Argument

The 4^th 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 2^nd sheet (e.g., Department B) are used as replacement values for the #N/A Errors from the lookup for the 1^st sheet (e.g., Department A).

=XLOOKUP(B3,'Dept. A'!$B$3:$B$7,'Dept. A'!$C$3:$C$7,F3)

Combining all these concepts results to our original formula:

=XLOOKUP(B3,'Department A'!$B$3:$B$7,'Department A'!$C$3:$C$7,XLOOKUP(B3,'Department B'!$B$3:$B$7,'Department B'!$C$3:$C$7))

 

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(B3,'Dept. A'!$B$3:$B$7,'Dept. A'!$C$3:$C$7,XLOOKUP(B3,'Dept. B'!$B$3:$B$7,'Dept. B'!$C$3:$C$7,XLOOKUP(B3,'Dept. C'!$B$3:$B$7,'Dept. C'!$C$3:$C$7)))