XLOOKUP Match Mode: Exact, Approximate, and Wildcards

In Excel, XLOOKUP’s match_mode is its fifth argument: use 0 for an exact match, -1 for an exact match or the next smaller item, 1 for an exact match or the next larger item, and 2 for wildcard matching. If you omit the argument, XLOOKUP uses exact matching by default.

What XLOOKUP Match Mode Controls

Match mode tells XLOOKUP what counts as a match when it compares your lookup value with values in the lookup array. It is different from the function’s sixth argument, search_mode, which controls the direction or method of the search. The distinction matters: match mode changes which values qualify, while search mode changes how Excel looks through them.

The basic syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The square brackets in this description mean that an argument is optional; do not type them into a formula. The lookup array and return array should correspond in size and orientation. For example, when searching down a column, return values should ordinarily be in a column with the same number of rows.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for Windows, Mac, and the web. It is not included in Excel 2019 or earlier. The formulas below use commas as argument separators, as in the standard English-language syntax; some regional settings display semicolons instead.

The Four Match Modes

Choose a mode based on the rule your data needs, not simply on which option seems most forgiving:

0 — exact match. Excel returns a value only when it finds the lookup value. This is the default, so 0 can be left out, but including it makes the intended rule visible.
-1 — exact match or next smaller item. If there is no exact value, XLOOKUP can return the result associated with a lower value.
1 — exact match or next larger item. If there is no exact value, it can return the result associated with a higher value.
2 — wildcard match. This lets a text lookup value use wildcard characters such as an asterisk or question mark.

Do not confuse -1 and 1 with search direction. They are match modes because they are in the fifth argument. Searching from the bottom upward is a search-mode choice, made in the sixth argument.

Exact Match: The Usual Choice for IDs

Exact matching is generally right for product codes, employee IDs, invoice numbers, and names when the lookup should identify the same value rather than a nearby one. XLOOKUP uses exact match by default, unlike the traditional VLOOKUP behavior many spreadsheet users associate with approximate matching when its final argument is omitted. Still, explicitly entering 0 can help a future reader understand the formula.

With IDs 101, 102, 104, and 107 in A2:A5, the corresponding regions East, West, North, and South in B2:B5, and lookup value 104 in D2, use this formula:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found",0)

The result is North, because 104 appears in A4 and the aligned return value in B4 is North. If D2 contained 103 instead, the formula would return Not found; exact matching does not silently substitute a neighboring ID.

The fourth argument, if_not_found, is separate from match mode. It supplies a custom result when there is no match. If you omit it, XLOOKUP returns the Excel error #N/A. A custom message can make a report easier to read, but it can also conceal a data problem if users mistake that message for a valid result.

Approximate Matches for Bands and Thresholds

Approximate modes are useful when a lookup value falls between breakpoints—for example, to assign a shipping tier, commission rate, or grade. The mode determines which neighboring breakpoint is eligible: -1 goes to the next smaller value, while 1 goes to the next larger value. The returned value comes from the same position in the return array as the selected breakpoint.

Suppose A2:A5 contains ascending minimum scores 0, 50, 75, and 90; B2:B5 contains Needs work, Developing, Pass, and Distinction; and D2 contains 82. With these cells, the formula is:

=XLOOKUP(D2,A2:A5,B2:B5,"No band",-1)

The result is Pass: 82 is not an exact breakpoint, and the next smaller breakpoint is 75. If D2 were 90, the result would be Distinction because that is an exact match. If it were below the smallest breakpoint, the formula would return No band because there is no smaller breakpoint available.

For the same ascending breakpoints, using 1 instead would select the next larger breakpoint. With D2 still equal to 82, this formula returns Distinction, associated with 90:

=XLOOKUP(D2,A2:A5,B2:B5,"No band",1)

That result is not automatically the better choice. Ask whether the business rule assigns a value to the band it has reached (usually the lower breakpoint) or to the next threshold above it. Check values below the first and above the last breakpoint too, since an approximate mode cannot supply a missing neighbor. Clear, ordered threshold tables make the rule easier to review even when using a linear search.

Wildcard Matching for Text

Use match mode 2 when the lookup text contains wildcard characters. An asterisk (*) stands for zero or more characters, and a question mark (?) stands for exactly one character. A tilde (~) is the escape character when you need to match a literal wildcard character rather than use it as a pattern.

With Maple, Market, and Meadow in A2:A4, their corresponding categories in B2:B4, and M* in D2, this formula returns the first matching category:

=XLOOKUP(D2,A2:A4,B2:B4,"No match",2)

The result is the category next to Maple, because Maple is the first item in the lookup range that begins with M. The wildcard does not mean “return every match”; XLOOKUP returns one result. To return multiple matching rows, a filter-based approach such as FILTER may suit the task better in Microsoft 365 or Excel 2021 and later.

Wildcard mode is intended for text-pattern matching, not approximate numeric lookup. Also note that ordinary XLOOKUP text matching is not case-sensitive: a pattern does not distinguish uppercase from lowercase. If case is part of the identification rule, use a case-sensitive method rather than assuming wildcard mode will enforce it.

Search Mode Is a Separate Setting

The sixth argument, search_mode, controls where or how XLOOKUP searches. Its default, 1, searches from the first item to the last. Use -1 to search from the last item to the first; this is useful when a list contains repeated keys and the latest or last-listed entry should be returned.

For example, with names in A2:A5, corresponding status values in B2:B5, and “Riley” in D2, a reverse search returns the status on Riley’s last listed row:

=XLOOKUP(D2,A2:A5,B2:B5,"Not found",0,-1)

This formula still uses exact match mode, 0. Only its search direction changes. If the key appears more than once, the result is the return value aligned with the last occurrence, not a combined result.

Search modes 2 and -2 use binary search on data sorted in ascending and descending order, respectively. They can be faster on large, correctly sorted ranges, but the data must be sorted as required. If it is not, XLOOKUP can return an incorrect result. These values are search modes, so a formula using binary search can still have a separate exact or approximate match mode.

How to Choose and Avoid Common Mistakes

Before writing the formula, decide what a correct result means. A unique code normally calls for exact matching. A threshold table calls for a deliberate choice between the next smaller and next larger breakpoint. A partial text pattern calls for wildcard mode. Then check the data and the result range rather than relying on a mode to fix inconsistent source data.

For a missing ID, check for leading or trailing spaces, text-versus-number differences, and mistyped characters before switching away from exact matching.
For a banded value, verify the breakpoint order, the result assigned to every breakpoint, and what should happen beyond the table’s endpoints.
For repeated keys, decide whether the first or last occurrence is intended; changing search direction does not make duplicate records unique.
For binary search, confirm the lookup array is sorted in the direction required by the selected search mode.
For a pattern that includes a literal asterisk or question mark, escape that character with a tilde so it is not treated as a wildcard.

A frequent mistake is placing a search-mode value in the fifth position. For example, -1 in argument five means “exact or next smaller,” while -1 in argument six means “search last to first.” Another is assuming that if_not_found affects approximate selection: it only handles the case where the chosen search and match rules find no eligible result.

XLOOKUP Compared with VLOOKUP and MATCH

VLOOKUP supports exact or approximate behavior through its final argument, but its approximate behavior and argument placement differ from XLOOKUP’s clearly named match-mode position. XLOOKUP can also return values from a range to the left or right of the lookup range without requiring a column index number. That flexibility can make a formula easier to maintain when columns move.

The older MATCH function returns a position rather than a corresponding value. XLOOKUP combines locating a value and returning an aligned result in one function, while XMATCH is a more direct position-finding counterpart with its own match and search mode options. Choose based on the result needed and the workbook’s compatibility requirements. If a file must work in Excel 2019 or older, XLOOKUP may not calculate there; a compatible alternative may be necessary.

Frequently Asked Questions

What is the default XLOOKUP match mode?
XLOOKUP defaults to 0, exact match. If there is no exact match, it returns #N/A unless you provide an if_not_found result.

Should I use 0 or -1 in XLOOKUP?
Use 0 when only an identical value is acceptable. Use -1 when an exact match or the next smaller value is valid, such as selecting the lower breakpoint for a score band.

What does match mode 1 do in XLOOKUP?
It returns an exact match if available; otherwise it uses the next larger item. It is useful only when that higher-neighbor rule matches the logic of your lookup.

How do I use wildcards in XLOOKUP?
Put the pattern in the lookup value and set the fifth argument to 2. Use * for any number of characters, ? for one character, and ~ to escape a wildcard character.

Does XLOOKUP match mode control search direction?
No. Match mode is the fifth argument and controls what counts as a match. Search mode is the sixth argument and controls search direction or binary-search behavior.

Why does XLOOKUP return the wrong approximate result?
Check whether the chosen next-smaller or next-larger rule matches your intended thresholds, whether the return values align with the lookup values, and whether you used binary search on data sorted in the required order.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.