XLOOKUP in Excel: The Modern Replacement for VLOOKUP

VLOOKUP has served Excel users for decades, but it has always had sharp edges: it can’t look to the left, it breaks if you insert a column, and it needs FALSE typed every time or it silently gives wrong answers. XLOOKUP fixes all of that. If you have Microsoft 365 or Excel 2021, XLOOKUP is the function you should reach for first.

What XLOOKUP does

XLOOKUP searches a range for a value and returns the matching value from another range. Unlike VLOOKUP, the return range doesn’t have to be to the right of the lookup range, and there’s no column number to count — you point at the two ranges directly.

The syntax

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

  • lookup_value — the value to find.
  • lookup_array — the column (or row) to search.
  • return_array — the column (or row) to return values from.
  • if_not_found — optional text to show when there’s no match, for example "Not found". This replaces the IFERROR wrapper VLOOKUP usually needs.
  • match_mode — optional: 0 = exact match (the default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match.
  • search_mode — optional: 1 = search top to bottom (default), -1 = search bottom to top, useful for finding the last match.

Step-by-step example

Same problem as VLOOKUP: a price list in columns A:B and an order list that needs prices.

  1. Click the cell where the price should go.
  2. Type =XLOOKUP(A2,$A$10:$A$14,$B$10:$B$14,"Not found").
  3. Press Enter. If the code isn’t in the list, the cell shows “Not found” instead of an ugly #N/A.
  4. Drag the fill handle down.

Notice what’s missing compared with VLOOKUP: no column number, no FALSE. The exact match is the default, which is the behaviour you want 95% of the time.

Looking to the left

This is the trick VLOOKUP simply cannot do. Suppose the price is in column A and the product code in column B. VLOOKUP fails because the lookup column must be first. XLOOKUP doesn’t care:

=XLOOKUP(A2,$B$10:$B$14,$A$10:$A$14)

The lookup array and the return array are independent ranges. The return can be on either side.

Finding the last match

When a list has duplicate entries — say, a price history where the same product appears several times — VLOOKUP always returns the first match. XLOOKUP with search_mode set to -1 searches from the bottom and returns the last match, which is usually the most recent price:

=XLOOKUP(A2,$A$10:$A$20,$B$10:$B$20,"Not found",0,-1)

Worked example

Product code Product Price
A1001 Notebook $4.50
A1002 Pen set $6.20
A1003 Stapler $8.90
A1004 Binder $3.75

=XLOOKUP("A1003",$A$2:$A$5,$C$2:$C$5) returns $8.90. And =XLOOKUP("A9999",$A$2:$A$5,$C$2:$C$5,"Not found") returns Not found instead of an error.

Common errors

#N/A. No match, and you didn’t supply if_not_found. Either the value really isn’t there, or you have the usual suspects: extra spaces, numbers stored as text, different capitalisation is fine (XLOOKUP is case-insensitive by default).

#VALUE! — arrays are different sizes. Your lookup array has 10 rows and your return array has 9. Both ranges must be the same size.

#SPILL!. XLOOKUP can return a whole range at once; if the cells it wants to spill into aren’t empty, you get #SPILL!. Clear the blocking cells.

The function doesn’t exist. XLOOKUP needs Microsoft 365, Excel 2021, or later. Excel 2019 and older don’t have it — your file will show #NAME? on those versions. Google Sheets does support XLOOKUP, so the same formula works there.

XLOOKUP vs VLOOKUP

VLOOKUP XLOOKUP
Looks left No Yes
Default match Approximate (dangerous) Exact
Column insert breaks it Yes No
Built-in “not found” text No Yes
Works in old Excel Yes No (2021+ only)

Practice file

Download the XLOOKUP practice file (.xlsx) — price list, left-lookup example, last-match example, and a blank sheet to try it yourself.

All amounts in this post are in nominal dollars — they are not adjusted for inflation. Example prices are illustrative, not market data.