XLOOKUP Examples: Exact, Approximate, and Wildcard Match

XLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For an exact match, put the lookup value, lookup range, and result range in the first three arguments; exact matching is the default, but you can specify 0 explicitly.

What XLOOKUP’s match argument does

The full XLOOKUP syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The two optional arguments at the end do different jobs: match_mode defines what counts as a match, while search_mode defines the direction or method Excel uses to search.

Match mode is the fifth argument, after the optional value to return when there is no match. Its available settings are:

0: exact match only. This is the default if you leave the argument out.
-1: exact match, or the next smaller item if there is no exact match.
1: exact match, or the next larger item if there is no exact match.
2: wildcard match, using characters such as * and ?.

These settings make XLOOKUP useful for more than finding an identical ID. It can also select a rate based on a threshold or find a text entry that fits a pattern. The examples below use Microsoft Excel for Microsoft 365 on Windows; XLOOKUP is also available in Excel 2021 and later, including supported Mac and web versions. Excel 2019 and earlier do not include the function.

Exact match: find a value in another column

Exact matching is the usual choice for product codes, employee IDs, invoice numbers, or other values that should be identical. Suppose A2:A5 contains product codes P-101, P-102, P-103, and P-104, while B2:B5 contains their prices: 12, 15, 18, and 21. Cell E2 contains the code P-103. With those ranges, use:

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

The result is 18, the value from the same position in the return range as P-103 in the lookup range. If E2 contains a code that is absent, the formula returns “Not found” instead of an error. The fourth argument is optional, but adding it makes a missing lookup easier to interpret.

Because exact match is the default, the shorter formula =XLOOKUP(E2,A2:A5,B2:B5,"Not found") returns the same result. Including 0 can still help readers understand the intended behavior, especially when a workbook also uses approximate or wildcard matching. XLOOKUP returns the first matching item it encounters unless you change the search direction.

Approximate match for thresholds and bands

Approximate matching is useful when a value belongs to a band rather than needing to equal a row exactly. A common example is a commission table whose first column lists minimum sales amounts. Assume A2:A5 contains 0, 1,000, 5,000, and 10,000, and B2:B5 contains the corresponding rates 0%, 3%, 5%, and 8%. The thresholds are sorted in ascending order. If E2 contains sales of 7,200, this formula finds an exact threshold or the next smaller one:

=XLOOKUP(E2,A2:A5,B2:B5,"No tier",-1)

The result is 5%: 7,200 is not an exact threshold, and 5,000 is the next smaller threshold. This is the typical pattern for choosing the applicable lower-bound band. The first value in the threshold range should normally be low enough to cover the smallest input; otherwise, a value below that first threshold has no exact or smaller match and the formula returns “No tier.”

The other approximate setting, 1, looks for an exact value or the next larger value. For example, suppose A2:A4 contains shipping breakpoints 1, 5, and 10 kilograms, while B2:B4 contains the service labels “Small,” “Medium,” and “Large.” If E2 contains 6 kilograms, this formula selects the next larger breakpoint:

=XLOOKUP(E2,A2:A4,B2:B4,"No service",1)

The result is Large, because 10 is the next listed breakpoint above 6. That may or may not fit a particular pricing policy: if a business assigns a weight to the highest breakpoint it has reached, a lower-bound table and match mode -1 may be the right design instead. The match setting does not decide what a band means; the order and meaning of the table do.

Wildcard match for partial text

Choose match mode 2 when the lookup value is a text pattern rather than a complete text value. The common wildcard characters are * for any number of characters, including none, and ? for exactly one character. A tilde escapes a wildcard when you need to search for a literal asterisk or question mark.

Assume A2:A4 contains “North Ridge,” “Northstar,” and “South Ridge,” and B2:B4 contains their corresponding region codes NR, NS, and SR. The lookup value “North*” should find the first name starting with North:

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

The result is NR, because “North Ridge” is the first matching item in the range. Since more than one entry can fit a wildcard pattern, the result depends on the order of the lookup range. If you need the last matching item instead, use the reverse search option described below.

Wildcard matching is different from simply typing part of a word into an exact-match lookup. Without match mode 2, “North*” is treated as literal text and ordinarily will not match “North Ridge.” XLOOKUP’s text matching is not case-sensitive, so changing the capitalization of the pattern does not make it a case-sensitive search.

Search direction and the last matching item

The sixth argument, search_mode, is not another match rule. Its default, 1, searches from the first item to the last. Setting it to -1 searches from last to first, which is a convenient way to return the latest occurrence in a list or the last row matching a repeated key.

Suppose A2:A5 contains ticket IDs T-20, T-21, T-20, and T-22, and B2:B5 contains their status values “Open,” “Open,” “Closed,” and “Open.” If E2 contains T-20, this formula searches upward from the bottom of the listed range:

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

The result is Closed, from the later T-20 row. The 0 still requests an exact match; the final -1 changes only the search direction. This is helpful when a table records repeated updates and the last row is the current state, provided the rows are arranged in the intended order.

XLOOKUP also accepts search modes 2 and -2 for binary searching in ascending- or descending-sorted lookup arrays. Those modes can be faster on very large, correctly sorted lists, but the data must be sorted in the required order. Using binary search on unsorted data can produce invalid results, so the normal first-to-last or last-to-first modes are safer for ordinary tables.

Choosing the right match mode

Before writing the formula, decide what “match” means for the task. An incorrect match mode can return a plausible-looking answer, which may be harder to spot than an error. Use these practical checks:

Use 0 for identifiers and exact labels. A product code that is absent should not silently map to a nearby code.
Use -1 for ascending lower-bound thresholds, such as a minimum quantity needed to qualify for a rate.
Use 1 when the desired fallback is the next higher item. Confirm that this interpretation matches the rules behind the table.
Use 2 only when a pattern is intentional. Check whether multiple rows could match and which one should be returned.
Use search_mode separately when you need the last duplicate or a binary search. Do not confuse it with match mode.

For approximate matching, inspect the lookup column as well as the formula. Thresholds need to be numeric, consistently typed, and arranged in the order required by the lookup design. A number stored as text may not behave like a numeric threshold. Blank cells, duplicate thresholds, or an omitted starting threshold can also make results confusing.

XLOOKUP compared with MATCH and XMATCH

A common source of confusion is the difference between XLOOKUP and the older MATCH function. MATCH returns a position in a range, not the corresponding value from a separate result range. XLOOKUP can return that corresponding value directly, which is why it often replaces a combination of INDEX and MATCH.

For example, suppose A2:A4 contains product IDs P-101, P-102, and P-103; B2:B4 contains prices 12, 15, and 18; and E2 contains P-102. XLOOKUP returns the price directly:

=XLOOKUP(E2,A2:A4,B2:B4,"Not found",0)

The result is 15. MATCH alone, using the same lookup range and value, returns 2, the position of P-102 within A2:A4, rather than the price:

=MATCH(E2,A2:A4,0)

That distinction matters because MATCH’s third argument is not equivalent to XLOOKUP’s match mode. In MATCH, 0 requests an exact match, while 1 and -1 have approximate-match meanings that depend on sort order. XLOOKUP’s settings are defined differently, so copying a number from one function into the other without checking can change the result. XMATCH is a newer position-returning function with match and search options more like XLOOKUP, but it still returns a position rather than a value from a return array.

Common errors and troubleshooting

If a formula returns #N/A, check whether the lookup value is actually present and whether the chosen match mode allows a nearby or wildcard result. The optional if_not_found argument can replace the error with a message, but it does not fix a mismatch in the underlying data. For example, a value with a trailing space or a number stored as text may look identical on screen while differing from the lookup item.

If Excel displays #NAME? or the formula is not recognized, the workbook may be open in a version that does not support XLOOKUP, such as Excel 2019 or earlier. A formula may also show an unsupported-function marker when opened in older software. In Microsoft 365 on Windows, formula argument separators are typically commas; regional settings can require semicolons instead. If Excel rejects an otherwise valid formula, check the list separator used by that installation.

When a formula returns an unexpected but valid result, test the lookup range for duplicates and confirm the search direction. For approximate modes, review threshold ordering and verify whether the table represents lower bounds or upper bounds. For wildcard mode, check for multiple pattern matches and remember that the first matching row wins under the default search direction. These checks usually reveal whether the problem is the data, the lookup rule, or the expected outcome.

Frequently Asked Questions

What is the default match mode in XLOOKUP?
The default is 0, meaning exact match. You can omit the fifth argument for an exact lookup or include 0 to make the choice explicit.

How do I make XLOOKUP return the last match?
Set the sixth argument, search_mode, to -1. Keep the fifth argument set to the match rule you need, such as 0 for an exact match.

Does XLOOKUP match text without regard to capitalization?
Yes. XLOOKUP’s normal text matching is case-insensitive. If capitalization must distinguish two entries, use a different case-sensitive approach rather than assuming a match mode changes case handling.

Can XLOOKUP find part of a word?
Yes. Use match mode 2 and a wildcard pattern such as "*ridge*" to find text containing “ridge.” If several cells fit the pattern, the search direction determines which matching row is returned first.

Why does approximate XLOOKUP return the wrong row?
Check the distinction between next smaller and next larger, then confirm that thresholds are sorted and represent the intended boundaries. Also verify that the lookup value and threshold cells use compatible data types.

Practical takeaway

For most lookups, start with exact match: it is XLOOKUP’s default and is safest for identifiers. Choose -1 or 1 only after defining how the table’s thresholds work, and choose 2 for deliberate wildcard searches. Treat search direction as a separate decision, especially when keys repeat. Keeping those roles distinct makes XLOOKUP formulas easier to build, audit, and explain to the next person who uses the workbook.

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.