Excel

How to Use XLOOKUP in Excel

XLOOKUP is the modern replacement for VLOOKUP — it can look in any direction, handles missing values cleanly, and doesn't break when columns shift. Here's how it works.

Do this with AI

Try Griddy free

Start in your browser. Also works with Excel and Google Sheets.

By Justin Freels/Updated August 20, 2026/4 min read

XLOOKUP was introduced in Microsoft 365 and Excel 2021 to replace VLOOKUP and HLOOKUP. It's cleaner, more flexible, and fixes every major complaint about VLOOKUP: it looks in any direction, has a built-in default for missing values, and doesn't require counting column numbers.

The syntax

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

The first three arguments are required:

lookup_value — what you're searching for
lookup_array — the column to search in
return_array — the column to return from

The last three are optional but useful:

[if_not_found] — what to show if there's no match (replaces IFERROR wrapping)
[match_mode] — 0 = exact (default), -1 = exact or smaller, 1 = exact or larger, 2 = wildcard
[search_mode] — 1 = first to last (default), -1 = last to first, 2 = binary search

Basic example

Look up a product price by product ID:

=XLOOKUP(D2, A2:A100, C2:C100)

Same as =VLOOKUP(D2, A:C, 3, FALSE) but without the fragile column number.

With a fallback for missing values

Instead of wrapping in IFERROR, use the fourth argument:

=XLOOKUP(D2, A2:A100, C2:C100, "Not found")

Looking to the left

VLOOKUP can't look left — XLOOKUP can. The lookup column and return column are independent:

=XLOOKUP(D2, C2:C100, A2:A100)

This returns column A values by searching column C — impossible with VLOOKUP.

Returning multiple columns

XLOOKUP can return an entire row of values by passing a multi-column return array:

=XLOOKUP(D2, A2:A100, B2:D100)

This spills results across three columns — Name, Department, and Salary in one formula.

XLOOKUP vs VLOOKUP

	VLOOKUP	XLOOKUP
Look left	✗	✓
Built-in fallback	✗	✓
Breaks when columns inserted	✓ breaks	✗ safe
Returns multiple columns	✗	✓
Available in	All versions	365 / 2021+ only

NOTE

If your workbook is shared with colleagues on older Excel versions, stick with VLOOKUP or INDEX MATCH — XLOOKUP will show as #NAME? on their end.

The Griddy way

The arguments are easy to forget in the right order, especially the optional ones. Just describe what you need:

"For each order, look up the customer's account tier from the Customers sheet using their email address, and show 'Unknown' if the email isn't found"

Griddy picks the right formula and handles the cross-sheet reference automatically.

XLOOKUP compatibility note

XLOOKUP is available in newer Excel versions, but a workbook shared with older desktop Excel or another spreadsheet application may not calculate it. Confirm the target environment before replacing every VLOOKUP. If compatibility is required, retain a tested VLOOKUP or INDEX/MATCH version and document the tradeoff.

Build XLOOKUP around a stable key, not a display label that may change. Keep the if_not_found message specific enough to distinguish a missing record from an intentional blank:

=XLOOKUP(E2,A:A,D:D,"Review: key not found")

If duplicate keys are possible, XLOOKUP returns one matching result according to its search mode; use FILTER or a duplicate report when every matching row matters. Test a normal match, a missing key, a duplicate, and a key with extra spaces before shipping the formula into a recurring report.

Sources

Do this with AI

Turn the instructions into a finished spreadsheet.

Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.

Try Griddy freeStart in your browser. No download required.

Also works withExcel Google Sheets

Use this on real templates

Use XLOOKUP in modern ops sheets

XLOOKUP is the cleaner pattern for pulling client, deal, or billing information into live sheets without fragile column indexes.

Sales

All posts