Xlookup Match Mode

XLOOKUP has quietly replaced VLOOKUP as the go-to lookup function in Microsoft 365, but most spreadsheet users only ever touch its first three arguments and ignore the fourth: match_mode. That single argument controls whether XLOOKUP demands an exact match, tolerates approximations, or hunts for wildcard patterns — and getting it wrong is the single most common reason XLOOKUP formulas return #N/A or silently pull the wrong row.

What match_mode actually controls

XLOOKUP’s full syntax is `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`. The match_mode parameter sits in position five, right after the optional `if_not_found` text you might use to replace `#N/A` errors.

Most people never touch it because Excel defaults to exact match behavior when the argument is omitted. But that default is exactly why so many users don’t realize the option even exists — until they need to look up a date range, a price tier, or a partial text string and their formula breaks.

Here’s the full breakdown of the four accepted values:

Analysts who train teams on Excel migrations often report that match_mode confusion is the top cause of “XLOOKUP gave me the wrong answer” support tickets — usually because someone assumed VLOOKUP’s approximate-match logic carried over automatically.

It doesn’t. VLOOKUP’s fourth argument is `TRUE`/`FALSE` and defaults to approximate match if left blank, which is exactly backwards from XLOOKUP’s exact-match default. That reversal alone trips up longtime VLOOKUP users switching over.

Exact match mode (0): the safe default

When match_mode is 0 or omitted entirely, XLOOKUP behaves like a stricter, more reliable version of VLOOKUP’s exact-match mode. It scans the lookup array for a value that matches your lookup_value precisely, and if nothing matches, it returns `#N/A` (or whatever you specified in the `if_not_found` argument).

This is the mode you want for the vast majority of everyday lookups:

• Matching an employee ID to a payroll record
• Pulling an invoice number’s status from a tracking sheet
• Returning a customer’s email address based on account number
• Cross-referencing SKUs between an inventory sheet and a sales log

The key advantage over VLOOKUP here isn’t the matching logic itself — it’s that XLOOKUP doesn’t require the lookup column to be the leftmost column in your range. You can look left, right, or anywhere, which eliminates the need for helper columns or the clunkier INDEX/MATCH combo that many Excel power users relied on for over a decade.

One subtlety: exact match in XLOOKUP is case-insensitive by default, same as VLOOKUP. If you need case-sensitive matching (rare, but it happens with product codes like “AB100” vs “ab100”), you’d need to wrap XLOOKUP in an `EXACT()` function check, since match_mode alone won’t handle that.

Approximate match modes (-1 and 1): the underrated power tool

This is where match_mode earns its keep. Modes -1 and 1 let XLOOKUP find the closest value when an exact match doesn’t exist, and the direction matters enormously depending on your data.

Mode -1: next smallest value

Set match_mode to -1 and XLOOKUP returns an exact match if one exists, or falls back to the largest value that’s still less than or equal to your lookup_value. This is the mode built for bracketed data — situations where you’re checking which range a number falls into.

Classic example: a sales commission table.

Sales Threshold | Commission Rate 0 | 2% 10000 | 4% 25000 | 6% 50000 | 8%

A formula like `=XLOOKUP(32000, A2:A5, B2:B5, “Not found”, -1)` returns 6%, because $32,000 falls between the $25,000 and $50,000 thresholds, and -1 mode grabs the next smallest match.

Mode 1: next largest value

Flip it to 1 and XLOOKUP does the opposite — it returns an exact match, or the smallest value that’s still greater than or equal to your lookup_value. This suits situations like minimum order quantities or grading thresholds where you want to round up rather than down.

• Use -1 when you’re asking “what tier does this fall into from below?”
• Use 1 when you’re asking “what’s the next requirement I need to meet?”

Both modes require your lookup array to be sorted — ascending for mode 1, and either direction works for -1 depending on how you pair it with search_mode, though ascending is the standard convention most spreadsheet builders follow.

Wildcard match mode (2): pattern matching without formulas

Match_mode 2 turns XLOOKUP into a text-pattern search tool, accepting the same wildcard characters Excel has used since the 1990s in functions like `SUMIF` and `COUNTIF`:

• `*` matches any number of characters
• `?` matches exactly one character
• `~` escapes a literal asterisk or question mark if your data actually contains one

A formula like `=XLOOKUP(“Invoice*”, A2:A100, B2:B100, “No match”, 2)` will find the first cell in column A starting with “Invoice,” regardless of what follows. This is invaluable for messy real-world datasets where entries look like “Invoice-2026-0451” or “Invoice #2026-0451-REV2” and you can’t guarantee consistent formatting.

Practical scenarios where wildcard mode shines:

1. Matching partial company names when a CRM export truncates or abbreviates entries differently across systems
2. Searching for any cell containing a specific keyword embedded in a longer string
3. Finding entries with a known prefix or suffix but unpredictable middle content
4. Cleaning up merged datasets from multiple vendors with inconsistent naming conventions

One catch worth flagging: wildcard mode only works when match_mode is explicitly set to 2. If you type `*` into a lookup_value without setting match_mode, XLOOKUP treats the asterisk as a literal character and (correctly) returns no match, which confuses people who assume wildcards work automatically like they do in `SUMIF`.

How search_mode interacts with match_mode

Match_mode is often discussed alone, but it works alongside the sixth argument, search_mode, which determines the direction XLOOKUP scans the array — and combining them incorrectly causes subtle bugs.

For approximate matches (-1 or 1 in match_mode), pairing with the wrong search_mode on unsorted data can produce inconsistent results — XLOOKUP won’t throw an error, it’ll just quietly return the wrong row. If your bracket table isn’t sorted and you’re using binary search modes (2 or -2), that’s the most likely silent failure point.

A safe rule for most spreadsheet builders: leave search_mode at its default of 1 unless you’re deliberately optimizing performance on a dataset with tens of thousands of rows, where binary search modes can meaningfully speed up calculation.

XLOOKUP match_mode vs. the old VLOOKUP approach

Anyone who spent years building VLOOKUP formulas knows the old approximate-match trick: set the fourth argument to `TRUE` (or leave it blank) and hope the lookup column was sorted ascending. XLOOKUP’s match_mode makes that logic explicit and far less error-prone.

The biggest practical win: VLOOKUP has no equivalent to match_mode 1. There’s simply no built-in way to search for “next largest value” in classic VLOOKUP — you’d need to sort your data descending and get creative, or drop into INDEX/MATCH with a custom match_type. XLOOKUP makes that a one-argument change.

Common mistakes and how to avoid them

Even experienced Excel users trip over match_mode in predictable ways. Knowing the failure patterns in advance saves debugging time later.

• Forgetting to sort data for approximate matches. Modes -1 and 1 rely on sorted lookup arrays for reliable, fast results. Unsorted data with search_mode set to binary search (2 or -2) will return wrong answers without any error message.
• Assuming wildcards work without match_mode 2. Typing `Smith` as a lookup_value does nothing special unless match_mode is explicitly set to 2.
• Confusing -1 and 1 directionality. It’s easy to mix up which mode rounds down versus up, especially since the negative number intuitively (but incorrectly) suggests “smaller,” while the logic is actually about the result direction, not the sign.
• Omitting if_not_found alongside approximate modes. If your lookup_value falls below the smallest threshold in a -1 mode table, XLOOKUP still returns `#N/A` unless you catch it with the fourth argument.
• Using match_mode 2 wildcards without escaping literal special characters. Product codes containing actual asterisks or question marks need the `~` escape character, or the formula misinterprets them as wildcards.

Practical formula examples worth saving

Here are ready-to-adapt formulas that showcase each match_mode in a realistic context:

1. Exact lookup with error handling: `=XLOOKUP(A2, Employees!A:A, Employees!C:C, “Employee not found”, 0)`
2. Tax bracket lookup (round down): `=XLOOKUP(income, Brackets!A:A, Brackets!B:B, “”, -1)`
3. Minimum quantity pricing (round up): `=XLOOKUP(qty, Pricing!A:A, Pricing!C:C, “”, 1)`
4. Fuzzy vendor name search: `=XLOOKUP(“” & B2 & ““, Vendors!A:A, Vendors!D:D, “No match”, 2)`
5. Combining with IFERROR for legacy compatibility: `=IFERROR(XLOOKUP(A2, Data!A:A, Data!B:B, , -1), “Out of range”)`

These patterns cover roughly 90% of the real-world match_mode use cases analysts encounter in finance, operations, and sales reporting workflows.

Conclusion

Match_mode is the difference between XLOOKUP being a slightly better VLOOKUP and XLOOKUP being a genuinely different, more capable lookup engine. Exact match (0) covers routine lookups just fine, but the moment your data involves ranges, tiers, thresholds, or inconsistent text formatting, modes -1, 1, and 2 do work that used to require nested IF statements, helper columns, or nervous VLOOKUP workarounds. The learning curve is small — four possible values, each with a clear job — but the payoff shows up every time you’re staring at a bracket table or a messy import and don’t want to build a lookup formula from scratch.

FAQ