How to Use XLOOKUP in Excel Between 2 Sheets

Using XLOOKUP between two sheets in the same workbook follows the same pattern as a single-sheet XLOOKUP, except lookup_array and return_array both reference the second sheet by name. Because the two ranges are selected independently rather than as one combined table, cross-sheet XLOOKUP formulas tend to be a little easier to read than their VLOOKUP equivalents.

This page covers the syntax, a couple of sheet-reference details worth knowing, and how Turbo Excel Lookup handles the same scenario without sheet-name syntax at all.

Both lookup_array (Products!B:B) and return_array (Products!D:D) reference the same second sheet, but they don't need to be adjacent columns or part of a single selected block the way VLOOKUP's table_array does. This makes it simple to pull a return column that sits several columns away from the lookup column, without needing to include every column in between.

1. On the destination sheet, click the result cell and type =XLOOKUP(.
2. Click the lookup_value cell on the current sheet, then type a comma.
3. Switch to the second sheet's tab and click-drag to select only the lookup column, then type a comma.
4. While still on the second sheet, select only the return column, then add the optional if_not_found text.
5. Close the parenthesis and press Enter.

Turbo Excel Lookup treats a two-sheet scenario the same way it treats a two-file scenario - by importing each sheet as either the Base File or Lookup Table and matching on the columns you select. This means there's no sheet-naming syntax to get right, and the workbook's actual sheet names never need to appear inside a formula at all, which is particularly useful if sheet names change over time as the workbook evolves.

A subtle but occasionally useful XLOOKUP pattern is building the formula's structure before the second sheet has been created, by typing the intended sheet name directly rather than clicking to select it. This lets you build a template formula in advance, provided the sheet is created with the exact matching name before the formula is used - Excel won't validate the sheet name's existence until the formula actually tries to calculate.

This kind of forward-planning isn't necessary with Turbo Excel Lookup, since the app only ever operates on files and sheets that are actively loaded, so there's no equivalent of a formula silently waiting for a sheet that hasn't been created yet.

In a workbook with many two-sheet XLOOKUP formulas, it helps readability to keep the naming pattern consistent - always referencing the sheet before the column, always using the same abbreviation style for sheet names. This consistency makes it much faster to scan a column of similar formulas and spot the one that references an unexpected sheet, which is often the first sign something was copied incorrectly.

Duplicating a sheet (right-click tab > Move or Copy > Create a copy) is a common way to start a new month or period from an existing template, but any cross-sheet XLOOKUP formulas on the duplicated sheet will still reference the original sheet's name unless manually updated. Checking and updating these references immediately after duplicating a sheet avoids a new period's report silently pulling data from the wrong source.

Cross-sheet XLOOKUP formulas read more clearly than their VLOOKUP equivalents since lookup and return ranges are specified independently rather than counted within one combined table. That clarity becomes especially valuable in workbooks maintained by more than one person over time.

It's also worth periodically reviewing cross-sheet formulas in a workbook that's actively being restructured, since sheets that get merged, split, or renamed during a reorganization are the most common trigger for a previously working reference to quietly break.