XLOOKUP in Excel: How to Use It with Examples

XLOOKUP is the modern replacement for VLOOKUP: it does the same job — find a value, return matching information — while removing the limits that made VLOOKUP frustrating. It can look in any direction, it matches exactly by default, and it handles “not found” inside the formula. If your Excel is Microsoft 365 or Excel 2021 or newer, this is the lookup formula to learn now.

The XLOOKUP syntax

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

• lookup_value — what you are searching for.
• lookup_array — the single column (or row) to search in. Just that column — not the whole table.
• return_array — the column to bring the answer from. It can be anywhere, including to the LEFT of the lookup column.
• [if_not_found] — optional: what to show instead of an error when there is no match.
• [match_mode] — optional: 0 = exact match (the default). You can usually leave this out.
• [search_mode] — optional: 1 = search from the top (default); -1 = search from the bottom, which finds the LAST match.

Only the first three arguments are required. Compare that with our VLOOKUP guide: no column-counting, no FALSE to remember.

A worked example

Using the same small price list as our VLOOKUP tutorial — codes in A2:A5, prices in B2:B5:

Code (A)	Price (B)
P101	250
P102	400
P103	175
P104	520

With the code you want in D2 (say P103), the formula is:

=XLOOKUP(D2, A2:A5, B2:B5, “Not found”)

Reading it aloud: “Find D2 in column A, return the matching value from column B, and show ‘Not found’ if there is no match.” The result is 175. Change D2 to P999 and, instead of an ugly #N/A error, the cell calmly shows “Not found”.

Advantage 1: It can look left

VLOOKUP can only return values from columns to the right of the lookup column. XLOOKUP does not care about direction: if prices were in column A and codes in column B, =XLOOKUP(D2, B2:B5, A2:A5) works exactly the same. No rearranging your table to suit the formula.

Advantage 2: Exact match is the default

The most common VLOOKUP mistake in history is forgetting the FALSE argument and getting approximate matches. XLOOKUP matches exactly unless you tell it otherwise, so the beginner error simply does not exist.

Advantage 3: Find the LAST match with search_mode -1

Suppose a sheet logs prices over time and the same code appears many times. To get the most recent entry, add the last argument:

=XLOOKUP(D2, A2:A100, B2:B100, “Not found”, 0, -1)

The -1 tells Excel to search from the bottom up, so it finds the last (newest) occurrence instead of the first. VLOOKUP cannot do this at all.

The errors you might still see

• #N/A — no match found, and you did not provide an if_not_found value. Add the fourth argument.
• #VALUE! — the lookup_array and return_array are different sizes (for example, A2:A5 against B2:B8). Make them the same height.
• #NAME? — your Excel version does not have XLOOKUP. It exists in Microsoft 365 and Excel 2021 onwards; older versions show this error. Google Sheets also supports XLOOKUP.

When to still use VLOOKUP

If you share files with people on old Excel versions, VLOOKUP travels better — every version of Excel for decades understands it. For your own work on a current version, XLOOKUP is simpler, safer, and more capable. Knowing both is ideal; our VLOOKUP tutorial with examples covers the classic formula in full.

Quick recap

=XLOOKUP(what, where to look, what to return, “message if missing”). Exact match by default, looks in any direction, and search_mode -1 finds the last match. If your Excel has it, make XLOOKUP your default lookup formula.