Jun 2, 2026

How to Use XLOOKUP in Excel (with Examples, 2026)

Excel Xlookup Spreadsheets How-to Formulas Productivity

XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP. It can look in any direction, returns clean results, and has built-in error handling. It’s available in Excel 365 and Excel 2021 or later — if you’re on an older version, use INDEX/MATCH instead.

XLOOKUP Syntax

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

lookup_value — the value to find.
lookup_array — the single column (or row) to search.
return_array — the column (or row) to return a result from.
if_not_found — (optional) text to show when there’s no match, instead of #N/A.
match_mode — (optional) 0 exact (default), -1/1 next smaller/larger, 2 wildcard.
search_mode — (optional) 1 first-to-last (default), -1 last-to-first.

1. A Worked Example

Column A has product IDs and column C has prices. To get the price for the ID in E2:

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

Notice you point directly at the return column — no counting column numbers like VLOOKUP. Press Enter.

2. Show a Message Instead of #N/A

Add the fourth argument to handle missing values gracefully — no need to wrap the whole thing in IFERROR:

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

3. Look to the Left

Unlike VLOOKUP, XLOOKUP doesn’t care about column order. If IDs are in column C and you want a name from column A:

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

It searches and returns in whatever direction you point it.

4. Return Multiple Columns at Once

Point return_array at several columns and XLOOKUP spills the whole matching row:

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

This returns the values from columns B, C, and D for the matched row in one formula.

5. Approximate Match

For bracket lookups (tax tiers, shipping bands), set match_mode to -1 (next smaller) or 1 (next larger):

=XLOOKUP(E2, A2:A100, C2:C100, "n/a", -1)

XLOOKUP does not require the data to be sorted for approximate match — a key advantage over VLOOKUP’s TRUE.

6. XLOOKUP vs. VLOOKUP

	VLOOKUP	XLOOKUP
Look left	No	Yes
Column index	Manual number	Point at the column
Default match	Approximate	Exact
Built-in “not found”	No (needs IFERROR)	Yes
Breaks if columns inserted	Often	No

7. Troubleshooting

#NAME? error

Your version of Excel doesn’t have XLOOKUP. It requires Excel 365 or 2021+. Use INDEX/MATCH on older versions.

#N/A error

No exact match was found and you didn’t set if_not_found. Add a message as the fourth argument, or check for stray spaces and text-vs-number mismatches.

#VALUE! error

lookup_array and return_array are different sizes. Make sure both ranges have the same number of rows (or columns).

Ready to automate your busywork?

Carly schedules, researches, and briefs you—so you can focus on what matters.

Get Carly Today →

Or explore our free tools