How to use xlookup in excel with two sheets

Answer

To use XLOOKUP in Excel with two sheets, you reference the lookup range and return range directly from the other sheet. XLOOKUP accepts full sheet-qualified references without requiring any additional configuration.

Understanding XLOOKUP Across Worksheets

XLOOKUP returns a value from a specified column or row after matching a lookup value. The function works across worksheets because Excel treats each sheet as a separate data container identified by its sheet name followed by an exclamation mark.

Basic Syntax

XLOOKUP uses the following structure:

=xlookup(lookup_value, lookup_array, return_array)

Parameters

1. lookup_value: The value you want to find.

2. lookup_array: The range that contains the value you want matched.

3. return_array: The range that contains the data to return.

Sheet references use this pattern:

SheetName!Range

Step-by-Step: Using XLOOKUP Across Two Sheets

Scenario

Sheet1 contains the value to search. Sheet2 contains the table where Excel will search and return results.

Steps

1. Click the cell in Sheet1 where the result should appear.

2. Type =XLOOKUP(.

3. Select the lookup value in Sheet1.

4. Type a comma.

5. Switch to Sheet2.

6. Highlight the lookup column.

7. Type a comma.

8. Highlight the return column in Sheet2.

9. Type ) and press Enter.

Example Formula

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)

This formula searches for the value in A2 on Sheet1, looks for it in column A of Sheet2, and returns the matching value from column B of Sheet2.

Optional Parameters

XLOOKUP supports optional arguments for match modes and search modes when needed.

Full Syntax

=xlookup(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Common Settings

• [if_not_found]: Custom message instead of an error.

• [match_mode]:

  • 0 — Exact match

  • 1 — Exact match or next larger

  • -1 — Exact match or next smaller

• [search_mode]:

  • 1 — Search from first

  • -1 — Search from last

Example With Options

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found", 0, 1)

This example returns "Not found" when no match exists.

Windows vs. Mac Differences

XLOOKUP works the same on Windows and Mac with these notes:

• Formula entry steps are identical.

• Sheet navigation, selection, and references behave the same across both platforms.

• Both versions support dynamic arrays and the full XLOOKUP function suite.

Best Practices for Accurate Cross-Sheet Lookups

1. Keep sheet names short and clear.

2. Avoid merged cells in lookup ranges.

3. Use whole-column references for tables with changing size.

4. Use defined tables (Ctrl + T) for cleaner structured references.

5. Use exact match mode (0) for consistent results.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report