Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 93 ยท Lookup and Reference

XLOOKUP and XMATCH

Learn every argument of Excel's XLOOKUP and XMATCH: not-found values, match modes, search modes, wildcards and multi-column results, and how they replace VLOOKUP.

XLOOKUP is the lookup function Excel should have had from the start. You give it the key, the range to search and the range to return, and you are done. There is no column number to count, no leftmost-column rule, and exact match is the default instead of a trap. Its partner XMATCH returns a position like MATCH, with the same improved options.

Both functions exist in Excel for Microsoft 365 and in Excel 2021 and later (including Excel for the web). They are not available in Excel 2019 or older, so a workbook you share with people on those versions will show #NAME?; there, stay with INDEX/MATCH from the previous lesson.

Excel cannot run on this site. The formulas are real Excel syntax; the live examples run a stand-in written in JavaScript, which implements the arguments and modes described here. It is a teaching model, not Excel's code, but it follows the documented rules for matching and ordering.

XLOOKUP at a glance

Key What to find.
Search range One row or column to look in.
Return range What comes back, any side.
Not found Your own message.

Exact match is the default.

1 / 2

The syntax

TEXT
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • lookup_value: the key.
  • lookup_array: a single row or column to search.
  • return_array: the cells to return from; it must have the same height (for a column) or width (for a row) as lookup_array. It can be to the left or the right, even on another sheet.
  • if_not_found: what to return instead of #N/A when nothing matches.
  • match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match.
  • search_mode: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending.

The simplest use replaces a VLOOKUP:

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

Compare it with =IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "Not listed"). No FALSE, no counted column, and the message is built in. Text comparison ignores case, like VLOOKUP.

Match modes and search modes in action

The example below defines xlookup and xmatch functions in JavaScript with the same six arguments and runs the interesting cases: not-found values, a lookup to the left, duplicates searched from either end, wildcards, and band lookups on unsorted limits. The code at the top is the stand-in itself; the experiments are in the second half.

JS XLOOKUP arguments: not found, left lookups, wildcards, bands

Press Run (or Ctrl + Enter) to see the console output here.

No output.

What it shows:

  • Not found. The fourth argument replaces #N/A, and without it you still get #N/A. Like IFNA, it only handles "not found", not formula mistakes.
  • A left lookup (name to ID) is just a matter of passing the ID column as the return array.
  • Duplicates. Search mode 1 returns the first P-200 (Book); search mode -1 starts at the bottom and returns the last one (Book (new)). Searching from the bottom is a neat way to get the latest entry in a log.
  • Wildcards need match_mode 2. B* matched the first name starting with B (Book, price 12), and ?amp matched Lamp. Without mode 2, * is just a character, so B* was not found. (To find a literal * or ? in wildcard mode, put a ~ before it.)
  • Bands. Match mode -1 returns the exact match or else the next smaller key, and 1 the next larger. Unlike approximate VLOOKUP, these linear modes do not require sorted data, so the unsorted limit list worked: 85 gave B with -1 (limit 80) and A with 1 (limit 90). If nothing qualifies, you get the not-found value.

Searching with binary mode

Search mode 2 (ascending) or -2 (descending) uses binary search, which is fast on huge sorted lists but, like approximate matching in the earlier lessons, only valid when the data really is sorted. The next example also returns several columns at once, builds a two-way lookup from two XLOOKUP calls and counts comparisons.

JS Multi-column results, two-way lookups and binary search mode

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Here is what the output tells you:

  • A whole row from one lookup. If return_array has several columns, XLOOKUP returns all of them for the matching row, and in a dynamic-array version of Excel the result spills into the neighboring cells (see Dynamic Arrays and Spilling). =XLOOKUP("P-200", A2:A4, B2:D4) is one formula instead of three.
  • A two-way lookup is XLOOKUP inside XLOOKUP: the inner call returns the whole row for the region, the outer picks the month from it. This is the counterpart of the INDEX/MATCH/MATCH pattern.
  • XMATCH with search mode -1 found the last OK (position 5 of 6), which is how you locate the latest entry.
  • Linear vs binary. A linear scan needed 100,000 comparisons for the last item. Binary search needed 17, but it must only be used on sorted data. On the messy list, binary mode did not find 30 even though it is there. Use mode 1 unless you know the data is sorted and the list is big.

XMATCH

TEXT
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

XMATCH is MATCH with XLOOKUP's modes. Exact match is the default (so no more forgotten 0), and it supports wildcards, reverse search and binary search. It returns a position, which you can feed to INDEX or to the newer array functions in ROWS, COLUMNS, TAKE, DROP, CHOOSECOLS and Reshaping Data.

Replacing VLOOKUP: a checklist

VLOOKUP habit XLOOKUP version
VLOOKUP(k, A:C, 3, FALSE) XLOOKUP(k, A:A, C:C)
IFNA(VLOOKUP(...), "none") XLOOKUP(k, A:A, C:C, "none")
VLOOKUP(k, A:C, 3, TRUE) XLOOKUP(k, A:A, C:C, , -1) (works unsorted too)
three VLOOKUPs for three columns one XLOOKUP(k, A:A, C:E) that spills
INDEX/MATCH for a left lookup XLOOKUP(k, C:C, A:A)

Note the empty fourth argument in the approximate row: the two commas skip if_not_found. Replace old formulas gradually. Workbooks that other people open in older Excel versions should keep INDEX/MATCH.

Mistakes to avoid

  • Mismatched sizes. The lookup and return ranges must be the same length. A misaligned pair (for example A2:A10 and C3:C11) returns neighbors' data, so double-check the first row.
  • Mixing up a row and a column. lookup_array must be one-dimensional. For a two-way lookup, use two nested lookups.
  • Hiding real errors. Use if_not_found for "no such key", not to hide a broken range. For multi-column return_array, the not-found value replaces the whole result.
  • Text versus numbers. The same pitfall as in VLOOKUP and Its Limits: "103" is not 103.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the default match behavior of XLOOKUP?

    match_mode defaults to 0, which is an exact match. This is one of the main safety improvements over VLOOKUP's TRUE default.
  2. 2. You need the LATEST row for a customer in a log where new rows are added at the bottom. Which search_mode do you use?

    search_mode -1 searches from the last item to the first, so the first match it finds is the bottom-most one.
  3. 3. Which match_mode lets you use * and ? in the lookup value?

    match_mode 2 enables wildcards. Without it, the * is treated as a plain character.
  4. 4. What does XLOOKUP(85, limits, grades, , -1) return, if limits is unsorted and holds 0, 60, 70, 80, 90?

    Match mode -1 returns the exact match or the next smaller item. The next smaller limit under 85 is 80, and linear search modes do not need sorted data.
  5. 5. Which Excel version cannot use XLOOKUP?

    XLOOKUP arrived with Microsoft 365 and Excel 2021. Excel 2019 and older show #NAME?, so use INDEX/MATCH when you share files with such users.

Summary

  • XLOOKUP(key, search_range, return_range, [not_found], [match_mode], [search_mode]) looks in any direction, defaults to exact match, and has a built-in not-found message.
  • Match modes: 0 exact, -1 next smaller, 1 next larger, 2 wildcard; the linear modes work on unsorted data.
  • Search modes: 1 first to last, -1 last to first, 2 or -2 binary search for sorted data.
  • A multi-column return range gives you a whole row (spilling) from one formula.
  • XMATCH is MATCH with the same modes; both need Excel 2021 or Microsoft 365.

Next: lookups that need more than one key at a time in Lookups with Multiple Criteria.