XLOOKUP’s match_mode argument controls what counts as a match: 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. For example, =XLOOKUP(E2,A2:A10,B2:B10,"Not found",-1) returns the result for E2’s exact value or, if there is no exact value, the next smaller one.
Where match mode fits in XLOOKUP
XLOOKUP looks for a value in one range and returns a corresponding value from another. Its full syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The square brackets mean those arguments are optional; they are not typed into the formula. Match mode is the fifth argument, after the optional message or value for a missing result.
For example, with product codes in A2:A10, product names in B2:B10, and the code to find in E2, the basic formula is =XLOOKUP(E2,A2:A10,B2:B10). Because the default match mode is exact, this searches for E2 exactly. You can make that choice explicit with =XLOOKUP(E2,A2:A10,B2:B10,"Not found",0). An explicit mode can make a workbook easier for another person to review.
The formulas below use commas as argument separators, as in standard English-language Excel settings. Some regional settings display semicolons instead. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not a built-in function in Excel 2019. The function works in supported Excel versions on Windows, Mac, and the web, though the interface and regional formula separators can differ.
Mode 0: exact match
Mode 0 tells XLOOKUP to return a result only when the lookup value matches an entry in the lookup range. This is the right choice for identifiers such as product codes, employee IDs, invoice numbers, and exact category names. It is also the default, so you do not have to include the fifth argument for a standard exact lookup.
Suppose A2:A5 contains P-101, P-102, P-103, and P-104, while B2:B5 contains Mouse, Keyboard, Monitor, and Cable. E2 contains P-104. With those codes and names in place, use:
=XLOOKUP(E2,A2:A5,B2:B5,"Not found",0)
The result is Cable. If E2 contains P-999 instead, the formula returns Not found, because the fourth argument supplies the result for a missing lookup. If you omit that argument, XLOOKUP returns the error #N/A when there is no match. An exact lookup is generally safer than approximate matching for identifiers: P-104 should not accidentally retrieve the details for P-103 simply because it is nearby in a list.
Mode -1: exact or next smaller
Use -1 when a value should qualify for the greatest available threshold that is less than or equal to it. This is useful for rate tables, tax-bracket-style bands, shipping charges, and other rules expressed as minimum thresholds. It does not mean “look one row up”; it means choose an exact match if one exists, otherwise use the next smaller value.
Suppose A2:A5 contains the ascending thresholds 0, 25, 50, and 100, and B2:B5 contains the corresponding fees 0, 3, 6, and 10. E2 contains 63. With that layout, enter:
=XLOOKUP(E2,A2:A5,B2:B5,"No band",-1)
The result is 6, because 50 is the greatest threshold no larger than 63. If E2 is exactly 50, the result is still 6. If E2 is below the smallest threshold, such as -1, there is no exact or smaller threshold, so the formula returns No band. Check the boundary behavior against the real rule your table represents; a lookup formula cannot decide whether an uncovered range should produce zero, a warning, or another result.
Mode 1: exact or next larger
Mode 1 is the counterpart to mode -1. It returns an exact match when available; otherwise, it chooses the next larger lookup value. It can help find the next qualifying size, deadline, or scheduled point above an input. Be careful when the business rule is actually “the largest threshold not exceeding the input”—that calls for -1, not 1.
For example, suppose A2:A5 contains quantity breakpoints 10, 25, 50, and 100, while B2:B5 contains 0%, 5%, 10%, and 15%. If E2 contains 18, the formula below finds the next breakpoint, 25, and returns its associated percentage:
=XLOOKUP(E2,A2:A5,B2:B5,"No later tier",1)
The result is 5%. That output is appropriate only if your rule says to use the next higher tier. If 18 units should instead receive the rate for the greatest breakpoint below 18, mode -1 would return the value associated with 10. This is why it helps to describe the rule in words before choosing a mode.
Mode 2: wildcard matching
Mode 2 enables Excel’s wildcard characters for text lookups. The asterisk (*) represents any number of characters, the question mark (?) represents one character, and the tilde (~) escapes a wildcard when you want to search for that character literally.
Suppose A2:A5 contains the surnames Anderson, Wilson, Patel, and Johnson, B2:B5 contains their department names, and E2 contains *son. With that layout, this formula searches for a surname ending in “son”:
=XLOOKUP(E2,A2:A5,B2:B5,"No match",2)
The result is the department beside Anderson, because it is the first matching entry in the lookup range. The wildcard pattern A* would match text beginning with A, while J?n would match three-character text beginning with J and ending in n. If a cell contains an actual asterisk and you need to find it literally, use ~* in the pattern; similarly, ~? searches for a literal question mark. Wildcards are for text patterns, not a substitute for approximate numeric matching.
Match mode is not search mode
A common source of confusion is that XLOOKUP has a separate sixth argument called search_mode. Match mode decides what qualifies as a match; search mode decides the direction or method used to search. For instance, -1 in the fifth argument means exact or next smaller, while -1 in the sixth argument means search from last to first. The same number has different meanings because it belongs to a different argument.
By default, XLOOKUP searches from the first item to the last. Search mode -1 searches from last to first, which can be useful when a list contains repeated keys and you want the last occurrence. Search modes 2 and -2 use binary search on data sorted in ascending and descending order, respectively. The data must be sorted as required for binary search; otherwise, the returned result may be invalid. For everyday lookups, the default search mode is usually the least error-prone. You can set both arguments explicitly, as in =XLOOKUP(E2,A2:A10,B2:B10,"Not found",0,-1), which uses exact matching while searching from bottom to top.
How to choose the right mode
Start by asking whether the lookup value must be identical to an entry, whether nearby numeric thresholds are meaningful, or whether you need to match a text pattern. Then select the mode that describes that rule. These examples summarize the decision:
- Use
0, or omit match mode, for an exact code, name, or other complete value. - Use
-1for an exact value or the closest available value below it. - Use
1for an exact value or the closest available value above it. - Use
2when the lookup value includes wildcards such as*or?.
For approximate threshold tables, keeping the values sorted in a clear order makes the rule easier to inspect and maintain. Sorting is mandatory for the binary search options in search mode, but match modes -1 and 1 are not the same thing as binary search. Avoid selecting a mode just because the table happens to be sorted: the direction should follow the meaning of the data. A discount based on the highest reached minimum usually needs -1; finding the next appointment after a date usually needs 1.
Common problems and checks
If a formula returns an unexpected result, first count the arguments. A missing if_not_found argument can shift a mode into the wrong position: =XLOOKUP(E2,A2:A10,B2:B10,0) makes 0 the missing-result value, not the match mode. To specify mode 0 explicitly, include the fourth argument, for example =XLOOKUP(E2,A2:A10,B2:B10,"Not found",0).
Also check that the lookup and return ranges have compatible dimensions. If the lookup range is A2:A10, the return range should normally cover the corresponding nine rows, such as B2:B10. Confirm that numeric-looking values are genuinely numbers in both the lookup cell and lookup range; text “50” and numeric 50 can behave differently in a workbook. For wildcards, verify that the fifth argument is 2 and that the pattern is in the lookup value. Finally, inspect duplicate entries: a normal first-to-last search returns the first matching item, even when several rows qualify.
XLOOKUP can return a custom missing result through if_not_found, but that argument does not repair a wrong match mode or bad source data. For auditing, use a visible message such as “No band” while building a threshold formula, and test values exactly on, between, and outside the listed breakpoints. Those edge cases often reveal a reversed mode or a gap in the table.
How XLOOKUP compares with older lookup formulas
VLOOKUP and HLOOKUP are familiar alternatives, but XLOOKUP separates the lookup range from the return range, so the return column does not have to sit to the right of the lookup column. It also uses exact matching by default, whereas older VLOOKUP examples often rely on a final argument to specify exact or approximate behavior. INDEX with MATCH can also perform flexible lookups, but it typically takes more formula structure to express the same task.
These alternatives may matter when sharing a workbook with people using older Excel releases that do not support XLOOKUP. A workbook containing XLOOKUP may show #NAME? in an older version that cannot recognize the function. In that case, use a compatible alternative such as INDEX and MATCH, or confirm that the recipient has a supported Excel version. Do not replace an exact lookup with an approximate legacy formula without checking its matching rules.
Frequently Asked Questions
What is the default XLOOKUP match mode?
It is 0, meaning exact match. You can leave the fifth argument out for an exact lookup, or include 0 explicitly after the optional if_not_found argument.
What is the difference between XLOOKUP match mode -1 and 1?
Mode -1 returns an exact match or the next smaller value. Mode 1 returns an exact match or the next larger value. Choose based on the threshold rule, not on which direction the table is sorted.
Does XLOOKUP match mode 2 mean approximate matching?
No. Mode 2 enables wildcard text matching with characters such as * and ?. Approximate numeric threshold lookups use -1 or 1.
Can XLOOKUP return the last matching duplicate?
Yes. Keep the desired match mode in the fifth argument and use search mode -1 in the sixth argument to search from last to first. For example, mode 0 with search mode -1 finds the last exact duplicate.
Why does XLOOKUP show #NAME? in my workbook?
The Excel version may not support XLOOKUP, as can happen in Excel 2019 or earlier. XLOOKUP is included in Microsoft 365 and Excel 2021 or later; use a compatible lookup formula if the workbook must work in an older release.
Related
- Heat Map Examples in PowerPoint: Ideas and How to Make Them
- Google Sheets for Data Analysis: Tools and Limits
- Google Sheets IF Formula: Examples and Common Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDIRECT Named Range: How It Works and When to Use ItIn Excel, INDIRECT turns text into a cell or range reference, so you can use it to refer…
- Excel SUMIF Not Blank: Sum Values When Cells Have DataWith the cells to check in A2:A10 and the numbers to add in B2:B10, use =SUMIF(A2:A10,"<>",B2:B10) to sum…
- How to Share an Outlook Calendar With the Right PermissionsTo share an Outlook calendar, open Calendar, choose Share Calendar, enter the recipient’s email address, set what they…
- XLOOKUP Match Mode: Exact, Approximate, and WildcardsIn Excel, XLOOKUP’s match_mode is its fifth argument: use 0 for an exact match, -1 for an exact…
- Excel Formula for the Higher of Two Values: MAX or IFTo return the higher of two values in Excel, use the MAX function. If the values are in…
- Excel Find and Replace Exact Match: What to KnowTo find or replace a cell only when its entire contents match, press Ctrl+H in Excel for Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.