What is XLOOKUP?

XLOOKUP is a flexible lookup function in Excel that searches for a value in one range and returns a corresponding value from another range. It replaces older functions (VLOOKUP/HLOOKUP) by allowing searches in any direction, returning exact matches by default, and handling missing values with a built-in if_not_found argument.

XLOOKUP Syntax

Use:

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

Components

• lookup_value — value to find

• lookup_array — range to search for the lookup value

• return_array — range to return the matching value from

• if_not_found (optional) — value to return if no match is found

• match_mode (optional) — 0 (exact), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard)

• search_mode (optional) — 1 (first→last), -1 (last→first), 2 (binary ascending), -2 (binary descending)

Step-by-Step: How to Use XLOOKUP

1. Arrange data in clear columns/rows; lookup and return arrays must match in size.

2. Click the cell for the result.

3. Type =XLOOKUP( then supply lookup_value, lookup_array, return_array.

4. Add optional if_not_found, match_mode, search_mode as needed.

5. Press Enter.

Example

=XLOOKUP(E2, A2:A100, B2:B100, "Not found", 0, 1)

Match Mode Options

• 0 — Exact match (default; returns #N/A if not found unless if_not_found is given)

• -1 — Exact or next smallest (requires ascending lookup_array)

• 1 — Exact or next largest (requires descending lookup_array)

• 2 — Wildcard (* and ? supported)

Search Mode Options

• 1 — First-to-last (default)

• -1 — Last-to-first (finds last occurrence)

• 2 — Binary search (ascending) — very fast for large, sorted ascending data

• -2 — Binary search (descending) — very fast for large, sorted descending data

Common XLOOKUP Use Cases

• Vertical lookups — search down a column and return values from another column.

• Horizontal lookups — search across a row and return values from another row.

• Two-way lookups — nest XLOOKUPs for row+column intersection.

• Multiple criteria — concatenate criteria (e.g., A2&B2) in lookup and lookup_value.

XLOOKUP vs Older Functions

• Vs VLOOKUP: searches left or right, defaults to exact match, no need for column index.

• Vs INDEX/MATCH: simplifies into a single function and supports additional search modes (e.g., binary search).

Troubleshooting Errors

• #N/A: lookup value not found — check spelling/extra spaces, or use if_not_found.

• #VALUE!: array sizes mismatch — ensure lookup_array and return_array have same length.

• #NAME?: function not recognized — XLOOKUP requires Excel 365 or Excel 2021.

Availability

• Windows: Excel 365 and Excel 2021 support XLOOKUP; earlier versions do not.

• Mac: Excel 365 for Mac and Excel 2021 for Mac support XLOOKUP.

Best Practices

• Organize data in tables with clear headers.

• Use exact match (0) for most lookups.

• Provide an if_not_found value to avoid #N/A messages.

• Lock ranges with $ when copying formulas.

• Use binary search modes for very large, sorted datasets for performance.

• Document complex XLOOKUP formulas with comments for future maintenance.

Notes

Test XLOOKUP formulas with known values before applying them widely. RetryClaude can make mistakes — please double-check responses.