# XLOOKUP – Multiple Sheets at Once – Excel & Google Sheets

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

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**

1 |
=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:

1 |
=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:

1 |
=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).

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

Combining all these concepts results to our original formula:

1 |
=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:

1 |
=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))) |