XLOOKUP across multiple Worksheets

Learn how to use XLOOKUP across multiple Worksheets with named ranges instead of the old VLOOKUP function.

Tip: If you frequently work with lookup functions and formulas, we recommend using the most effective solution, XLOOKUP.

LOOKUP Formula in Excel with multiple Sheets

This guide will show you how to summarize information from multiple Worksheets into one Worksheet. The first part of the tutorial will demonstrate the XLOOKUP method with easy error handling. In the second part of the tutorial, we’ll use a VLOOKUP function-based solution. With the help of named ranges, we can simplify the task and make the process easy to understand.

How to use XLOOKUP across multiple Worksheets

In the picture below, you can see our initial data set, and our goal is to look up values across three Worksheets.

We store the Total Sales in three different Worksheets that contain different products. We’ll use a named range to simplify working with formulas. It is the right decision if you do not want to keep long cell references in mind.

Create a named range

To add a named range, use the steps below:

Select the range you want to describe and enter the name in the name box to create a named range.

This small step saves you time in case of using complex formulas!

Apply names for Worksheet “Region2” and “Region3”.

Here is the formula to apply lookup across multiple Worksheets:

=XLOOKUP(A2,Products1,Sales1, XLOOKUP(A2,Products2,Sales2, XLOOKUP(A2,Products3,Sales3)))

Handling the #N/A error

Take a look at the helper table! If you want to validate your formulas, using the 4th argument of XLOOKUP is worth using.

In the example, examine the result of cell E2!

Formula:

=XLOOKUP(A2, Products2, Sales2, “-“)

The formula tries to lookup “Product6” in the Worksheet “Region2”. The formula returns with a text string, “-” if nothing is found.

Next, apply the formula for all cells in the range D2:F6.

It’s easy to check that two values come from the first Worksheet, one from the second, and the last two from the third.

The above-mentioned nested XLOOKUP formula does the rest and returns the proper values.

Workaround with Multiple Worksheets and VLOOKUP

If your Excel version does not support the latest lookup functions, there is a workaround with VLOOKUP. We’ll work with the current example (see above).

Our goal is to use a VLOOKUP-based formula across two Worksheets.

Because VLOOKUP uses different arguments than XLOOKUP, we need to create a new named range.

Formula:

=IFERROR(VLOOKUP(A2,SalesData1,2,FALSE),VLOOKUP(A2,SalesData2,2,FALSE))

Evaluate the formula from the inside out! We’ll check two cases: