XLOOKUP across multiple Worksheets

Learn how to use XLOOKUP across multiple Worksheets with named ranges instead of the old VLOOOKUP 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 don not wants to keep long cell references in mind.

lookup multiple Worksheets in Excel

Create a named range

To add a named range, use these 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 your time in case of using complex formulas!

create a named range.

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

Here is the formula

Handling the #N/A error

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

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.

xlookup error handling

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.

VLOOKUP named range for multiple Worksheets

Formula:

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

iferror-lookup-formula-for-multiple-worksheets

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

=IFERROR(value if found, value if not found)

=IFERROR(VLOOKUP formula1, VLOOKUP formula 2)

  • Case 1: The VLOOKUP function tries to find the lookup value in the second column of the “Salesdata1” range. If an exact match is found, return the value.
  • Case 2: If the exact match is not found, the formula jumps to the second Worksheet and lookup the correspondent value for Product1.

Download the practice file.

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.