XLOOKUP Match Modes Explained Clearly

XLOOKUP’s match modes control how Excel finds a match when an exact one doesn’t exist. You choose the mode in the fourth argument of the function, and it decides whether to return the next smaller item, the next larger item, an exact match, or a wildcard match. This article explains each mode, when to use it, and how to avoid common mistakes.

When to Use Each Match Mode

Each mode solves a different problem. Use:

• 0 (exact) – When you need an exact ID, code, or name. This is the default and the most common choice.
• -1 (next smaller) – For lookup tables with tiers, such as tax brackets, shipping weights, or discount thresholds. The lookup value falls into a bracket, and you want the lower bound.
• 1 (next larger) – When you need to round up to the next available option, like finding the next train departure time after a given time.
• 2 (wildcard) – When you need partial matching, like finding a product name that contains “blue”.

Common Mistakes and Misconceptions

One frequent error is using -1 or 1 without sorting the lookup_array. For -1, the array must be sorted ascending; for 1, descending. If not, XLOOKUP may return a wrong value instead of an error, which is harder to spot.

Another misconception is that -1 and 1 always return the nearest value. They don’t. They return the next smaller or next larger, not the closest. For example, with lookup_value 38 and array {10, 20, 30, 40}, -1 returns 30, 1 returns 40, but the closest is 40 (distance 2 vs 8).

Wildcard mode (2) is often confused with exact match. It only works with text. If the lookup_value is a number, wildcards are ignored and it behaves like exact match.

How to Choose the Right Match Mode

Ask yourself: “What should happen if the exact value isn’t found?”

• If the answer is “show an error” → use 0.
• If the answer is “use the lower bracket” → use -1.
• If the answer is “use the next available option” → use 1.
• If the answer is “match partially” → use 2.

Also consider the data type. For numeric lookups, -1 and 1 are common. For text, 0 or 2 are typical. For dates, -1 and 1 work well when you need the previous or next date.

Comparing XLOOKUP Match Modes to VLOOKUP and INDEX/MATCH

VLOOKUP’s fourth argument (range_lookup) is similar but less flexible. TRUE in VLOOKUP means “approximate match” (like -1, but it always uses next smaller and requires ascending sort). FALSE is exact match. XLOOKUP’s -1 and 1 give you both directions, and 2 adds wildcards. INDEX/MATCH offers more control but requires a separate MATCH function with its own match_type argument (0, 1, or -1). XLOOKUP simplifies this into one function.

Frequently Asked Questions

What does match_mode -1 do if the lookup value is smaller than the smallest array value?

It returns #N/A because there is no “next smaller” value. For example, looking up 5 in {10, 20, 30} with -1 gives #N/A.

Can I use match_mode 1 with an ascending sorted array?

Technically it will still return a result, but it may be incorrect. For match_mode 1, the array should be sorted descending. If it isn’t, the result is unpredictable. Always sort according to the mode you use.

Does match_mode 2 work with numbers?

Wildcards only apply to text. If the lookup_value is a number, XLOOKUP treats it as a literal and performs an exact match. To use wildcards with numbers, convert them to text first.

What’s the difference between match_mode -1 and VLOOKUP’s TRUE?

They are similar: both return the next smaller value and require ascending sort. However, XLOOKUP’s -1 also works with columns to the left, which VLOOKUP cannot do.

Why does XLOOKUP return a wrong value with match_mode -1?

Most likely because the lookup_array isn’t sorted ascending. Even one out-of-order value can cause incorrect results. Check the sort order and also ensure there are no blank cells or text in the array.

Can I use match_mode 0 with sorted data?

Yes, exact match works regardless of sort order. It simply scans for an exact match and returns the first one found. Sorting doesn’t affect the result, but it may affect performance on large datasets.

Related

• Combine Multiple Expressions in awk
• TP-Link Kasa and Tapo Devices: Are They Merging?
• Excel’s Smartest Visual Tools: Ignoring Them Costs You