XLOOKUP Wildcards: Examples, Rules, and Common Fixes

With region names in A2:A6 and matching IDs in B2:B6, use =XLOOKUP("North*",A2:A6,B2:B6,"Not found",2) to return 101, the ID on the first matching row. The final argument value 2 enables wildcard matching; use * for any number of characters and ? for one character.

How XLOOKUP wildcard matching works

XLOOKUP normally looks for an exact match. Wildcards are not automatically active: you must set the optional fifth argument, match_mode, to 2. In that mode, XLOOKUP interprets wildcard characters in the lookup value as a pattern and searches for the first cell that matches it.

* represents zero or more characters. The pattern North* can match North, Northeast, or North-West.
? represents exactly one character. The pattern INV-??? can match INV-204, but not INV-27.
~ escapes a wildcard so Excel treats it as a literal character. Use ~* to look for an asterisk and ~? to look for a question mark.

Wildcard matching is useful when a complete label is unknown or when you want a prefix, suffix, or contained phrase to identify a result. It matches text patterns, not numeric ranges. XLOOKUP is case-insensitive, so a pattern such as west* can match West or WEST.

Understanding the XLOOKUP arguments

The arguments most relevant to wildcard lookups are the value or pattern to find, the lookup range, the range to return a result from, an optional not-found message, and the match mode. The lookup and return ranges should line up row by row: if the lookup range is A2:A20, the corresponding return range might be B2:B20.

The fifth argument controls what counts as a match. A value of 0 means exact match and is the default; -1 and 1 request exact-or-next-smaller and exact-or-next-larger matches; 2 turns on wildcard matching. These modes are not interchangeable. For example, approximate match mode does not mean “find text that contains these letters.”

The sixth argument, search_mode, controls search direction or a binary search. If you leave it out, XLOOKUP searches from the first item to the last. That default matters when several cells match a wildcard pattern, because the first matching row supplies the result.

Examples: starts with, contains, and ends with

For a prefix search, the region-and-ID example in the opening matches the first name beginning with North. Because * can stand for zero characters, the exact text North itself qualifies as well as longer names. If the first row were Northeast instead, that row would be returned before later matches.

To find a phrase anywhere within a cell, put an asterisk on both sides. With descriptions in A2:A5—Invoice – West, East invoice, Central, and Invoice pending—and their reference numbers in B2:B5, use =XLOOKUP("*invoice*",A2:A5,B2:B5,"No match",2). It returns the reference number for Invoice – West because that is the first matching description. The wildcard does not rank results by how closely they fit; it follows the order of the lookup range.

For an ending pattern, put the asterisk first. With product names in A2:A5—North Ltd, South Ltd, North LLC, and East Ltd—and account IDs in B2:B5, =XLOOKUP("*Ltd",A2:A5,B2:B5,"No match",2) returns the ID for North Ltd, the first name ending in Ltd. If there is a space before Ltd in the data, include that space in the pattern, as in "* Ltd".

Examples: one-character and fixed-format patterns

Question marks are useful when a code has a fixed structure but one or more characters are unknown. With codes in A2:A4—INV-204, INV-27, and INV-318—and amounts in B2:B4, the formula =XLOOKUP("INV-???",A2:A4,B2:B4,"No match",2) returns the amount for INV-204. The three question marks require exactly three characters after the hyphen, so INV-27 does not qualify. If both INV-204 and INV-318 match, the first one wins.

You can combine wildcard types. Suppose A2:A5 contains AX-14, AB-8, AB-42, and BC-14, while B2:B5 contains corresponding descriptions. With this layout, =XLOOKUP("A?-1*",A2:A5,B2:B5,"No match",2) finds the first code that begins with A, has any one character next, then -1, and can have any characters after that. AX-14 fits and is returned. Patterns are evaluated against the entire cell text, but an asterisk at the end allows remaining characters.

Finding the last matching row instead

Sometimes the first match is not the useful one. For example, a log may contain repeated codes and you may want the latest matching entry, assuming newer entries are lower in the sheet. The sixth XLOOKUP argument can reverse the search direction using -1.

With event codes in A2:A6—AB-1, BC-1, AB-2, CD-1, and AB-3—and timestamps or values in B2:B6, use =XLOOKUP("AB*",A2:A6,B2:B6,"No match",2,-1). It returns the value from the AB-3 row, the last match when searching upward from the bottom. This finds the last matching position in the range; it does not determine which date is newest unless the rows are arranged accordingly.

Looking up literal asterisks, question marks, or tildes

A common surprise is that an asterisk in the lookup value is treated as a wildcard in match mode 2. To find a cell that actually contains an asterisk, put a tilde immediately before it in the pattern. With labels in A2:A4—Plan*, Plan A, and Plan?—and codes in B2:B4, =XLOOKUP("Plan~*",A2:A4,B2:B4,"No match",2) returns the code beside the literal text Plan*.

Likewise, the pattern "Plan~?" looks for the literal text Plan? rather than any five-character text beginning with Plan. To match a literal tilde, double it in a wildcard pattern as ~~. When building patterns from user-entered values, consider whether the input might itself contain *, ?, or ~; otherwise Excel may interpret those characters as pattern instructions.

How to choose a pattern and avoid ambiguous results

Start by deciding which part of the text is known. Use a trailing asterisk for a beginning, a leading asterisk for an ending, and asterisks on both sides for text anywhere inside the cell. Use question marks only when the number of unknown characters is fixed. A short pattern such as *1* may match many unrelated entries, so a more specific pattern usually makes the result easier to trust.

Use a prefix pattern such as "ACCT-*" for a family of account codes.
Use a suffix pattern such as "*@example.com" to find a matching email domain.
Use a contained phrase such as "*delayed*" when the phrase may appear at different positions.
Use a fixed number of question marks for consistent code formats, and check that the source data follows that format.

If several rows fit, XLOOKUP returns one result rather than listing every match. By default it returns the first match from top to bottom. Sort order, duplicate records, and the position of an especially broad pattern can therefore change which result you see. Make the lookup range and return range the same height and aligned to the same records; mismatched ranges can return a plausible result from the wrong row or cause an error.

Common problems and useful alternatives

If a wildcard formula returns #N/A, first check that the fifth argument is 2 and that the pattern actually fits the stored text. Look for extra spaces, punctuation, inconsistent code lengths, or a wildcard entered in the wrong position. A formula with "North*" will not find a value that has different text before North. The optional not-found argument can replace #N/A with a clearer message, but it cannot correct a pattern that does not match.

Wildcard matching is not a general-purpose “contains” test unless the pattern includes the surrounding asterisks. For instance, "invoice" in wildcard mode is still a whole-cell pattern; use "*invoice*" to allow other text before or after it. Also check that the lookup values are text. A displayed number may be stored as a number, and a text pattern should not be assumed to match numeric data in the same way it matches a text code.

If you need every matching row rather than the first result, consider FILTER with SEARCH. With descriptions in A2:A5, related values in B2:B5, and the search phrase in E2, =FILTER(A2:B5,ISNUMBER(SEARCH(E2,A2:A5)),"No matches") returns all rows whose descriptions contain the phrase, or No matches if none do. SEARCH is case-insensitive. FILTER is available in Microsoft 365 and Excel 2021 or later, as is XLOOKUP; older Excel versions may not support these functions. The functions work in Excel for Windows, Mac, and the web where supported, though menus and interface details can vary.

Frequently Asked Questions

Does XLOOKUP support wildcards?
Yes. Set the fifth argument, match_mode, to 2, then use * for any number of characters or ? for exactly one character. XLOOKUP uses exact matching by default, so the wildcard mode must be specified.

How do I do a partial match with XLOOKUP?
Use an asterisk around the known text and set wildcard match mode. For example, a pattern like "*west*" searches for west anywhere in a text cell. If you only know how the cell begins, put the asterisk after the known prefix instead.

How do I make XLOOKUP return the last wildcard match?
Set match_mode to 2 and the sixth argument, search_mode, to -1. XLOOKUP then searches from the bottom of the lookup range toward the top and returns the last matching row in that direction.

Can I search for an actual asterisk with XLOOKUP?
Yes. In wildcard mode, precede the asterisk with a tilde in the pattern, using ~*. Use ~? for a literal question mark and ~~ for a literal tilde.

Why does my XLOOKUP wildcard formula return #N/A?
Check that the fifth argument is 2, that the lookup pattern matches the text as stored, and that the lookup range contains text rather than a differently typed value. Also inspect spaces and punctuation. An if-not-found message can make a missing match clearer, but it will not make an incorrect pattern work.

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.