To find a value when you know only part of the text, use XLOOKUP with wildcard match mode 2—for example, with product names in A2:A6, prices in B2:B6, and the search text in D2, enter =XLOOKUP("*"&D2&"*",A2:A6,B2:B6,"Not found",2). If D2 contains cable, this searches for the first product name containing that text and returns its price.
What XLOOKUP wildcards do
Wildcards stand in for unknown characters in a text lookup. They are useful when a label includes extra words, when a name is incomplete, or when you want to find entries sharing a text pattern. XLOOKUP supports wildcards when its match mode is set to 2. Without that argument, XLOOKUP uses exact matching by default, so wildcard characters in a lookup value are not treated as wildcard instructions.
The wildcard characters Excel recognizes are:
*matches any number of characters, including no characters. For example,North*can match “North,” “North Office,” or “Northeast.”?matches exactly one character. For example,R?Dcan match “RED” or “RAD,” but not “ROAD.”~is the escape character. Put it before an asterisk or question mark when you mean the character itself rather than a wildcard.
These rules apply to text matching. Wildcards are not a general way to find approximate numeric values; for numeric bands, use XLOOKUP’s other match modes or a different lookup design.
Basic XLOOKUP syntax and match mode
XLOOKUP has the following argument order: lookup_value, lookup_array, return_array, optional if_not_found, optional match_mode, and optional search_mode. For a wildcard lookup, the fifth argument is the important one: use 2 for wildcard matching. The fourth argument can provide a clearer message if no match is found.
For example, with product descriptions in A2:A6 and prices in B2:B6, this formula searches for any description containing “cable” and returns the price from the same row:
=XLOOKUP("*cable*",A2:A6,B2:B6,"Not found",2)
If the first matching description is “USB-C Cable 2m” and its price is 12.5, the result is 12.5. The example assumes that this is the first matching row in the lookup range. XLOOKUP returns a value from the corresponding position in the return range; the lookup and return ranges therefore need to line up in size and orientation.
XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The examples here are for Excel for Microsoft 365 on Windows. The formulas and wildcard rules work the same way in current Excel for Mac and Excel for the web; ribbon layout and some interface details can vary.
Find text that contains a word or phrase
The contains pattern places an asterisk on both sides of the search text. This is the most common wildcard lookup because it can locate a phrase anywhere inside a longer cell value. It is often useful for product descriptions, customer names, address fragments, and category labels.
Suppose A2:A6 contains USB-C Cable 2m, HDMI Adapter, Ethernet Cable 5m, Desk Lamp, and Cable Ties Pack, while B2:B6 contains their prices. With the search term in D2, use:
=XLOOKUP("*"&D2&"*",A2:A6,B2:B6,"No matching product",2)
If D2 is cable, the formula returns the price for “USB-C Cable 2m,” assuming the list is in the order shown. It does not skip to “Ethernet Cable 5m” or “Cable Ties Pack,” even though those also contain the word. XLOOKUP returns the first match it encounters by default.
The ampersands join the wildcard characters to the cell value. This makes the formula reusable: change D2 to a different search term without editing the formula. If D2 is blank, the pattern becomes two asterisks, which can match any text value, so consider adding a check for an empty input if users might leave the search cell blank.
Match text at the beginning or end
Put an asterisk only where the unknown portion should be. A wildcard after the search term finds text that starts with the term; one before it finds text that ends with the term. These patterns are more selective than putting an asterisk on both sides.
With department codes in A2:A20 and department names in B2:B20, this formula finds the first code beginning with the text entered in D2:
=XLOOKUP(D2&"*",A2:A20,B2:B20,"No department found",2)
If D2 contains FIN, a code such as FIN-04 can match. A code with FIN only in the middle does not fit this pattern. To find a code ending in the D2 text, reverse the arrangement:
With the same code and department ranges, and D2 containing the ending characters, use:
=XLOOKUP("*"&D2,A2:A20,B2:B20,"No department found",2)
This can match a code such as OPS-02 when D2 contains 02. Note that a prefix or suffix lookup can still return an unexpected result if multiple codes share that pattern. Make the pattern as specific as the data requires.
Use question marks for a fixed character count
A question mark is useful when the position of a character is known but its value is not. For instance, a reference code that always starts with AB, has two variable characters, and ends in 7 can be represented by AB??7. Each question mark accounts for one character, not a variable-length section.
With codes in A2:A30 and corresponding status values in B2:B30, this formula looks for a code matching that five-character pattern:
=XLOOKUP("AB??7",A2:A30,B2:B30,"Code not found",2)
If the first code matching the pattern is ABXQ7 and its status is Active, the formula returns Active. A four- or six-character code will not match that pattern. This makes question marks handy for structured identifiers, but they should not be used when the number of unknown characters can vary; use an asterisk for that case.
Search for a literal asterisk or question mark
Because * and ? have special meanings in wildcard mode, search for a literal symbol by prefixing it with a tilde. For example, ~* means an actual asterisk, while ~? means an actual question mark. The tilde itself can be escaped by doubling it as ~~.
With item labels in A2:A10 and matching categories in B2:B10, this formula finds the literal text “Item* A,” rather than treating the asterisk as a wildcard:
=XLOOKUP("Item~* A",A2:A10,B2:B10,"Not found",2)
If A5 contains exactly Item* A and B5 contains Special, the formula returns Special, provided A5 is the first matching entry. Escaping is particularly important when importing filenames, notes, or identifiers that may contain punctuation. If a pattern behaves as if it is matching too broadly, check whether a wildcard needs a tilde.
Control which match XLOOKUP returns
Wildcard matching does not mean “best match.” XLOOKUP returns the first value that satisfies the pattern, scanning from the first item to the last by default. It does not rank results by how closely they resemble the typed text. If several records match, arrange the data or narrow the pattern so the first result is the intended one.
You can ask XLOOKUP to search from the bottom upward by setting its sixth argument, search_mode, to -1. With ticket descriptions in A2:A100 and assigned teams in B2:B100, this formula returns the team for the last description containing “printer”:
=XLOOKUP("*printer*",A2:A100,B2:B100,"No ticket found",2,-1)
This is useful when later rows represent newer records and you want the most recently listed match. It is not the same as finding the latest date: the rows must actually be ordered in the way you intend. If they are not, sort or otherwise identify the latest record rather than assuming a reverse search understands dates.
Like normal XLOOKUP matching, wildcard matching is not case-sensitive. A search for cable can match “Cable” as well as “cable.” If letter case is significant, standard XLOOKUP wildcard matching is not enough; a case-sensitive approach requires a different formula design.
Common problems and practical checks
A wildcard formula that returns #N/A or an unexpected result usually has a small, fixable cause. Check the pattern, match mode, and data before changing the lookup structure.
- Match mode is missing: Make sure the fifth argument is
2. Otherwise, XLOOKUP is looking for an exact value rather than interpreting the asterisk and question mark as wildcards. - Ranges do not align: The lookup and return ranges should have the same number of rows or columns. A one-row offset can return a valid-looking result from the wrong record.
- Spaces or inconsistent text: A trailing space in a source value can affect an exact pattern, and imported data may contain nonprinting characters. Inspect the underlying cell contents and clean the source data if needed.
- More than one match exists: The default is the first match, not the most relevant one. Use a more specific pattern or deliberately choose forward or reverse searching.
- Numbers are stored differently: A wildcard pattern is intended for text matching. If codes that look numeric are stored as numbers in some rows and text in others, normalize the data before relying on text patterns.
- The lookup cell is empty: Joining an empty cell to two asterisks can create a pattern broad enough to match an unintended entry. Require a search term before running the lookup if blank input is possible.
The optional if_not_found argument lets you show a readable result such as “No matching product” instead of the default #N/A. It does not fix a pattern that is too broad or a range mismatch, so use a helpful message while still checking the lookup logic.
When to use wildcard XLOOKUP instead of another method
Wildcard XLOOKUP is a good fit when one matching result is needed from a pattern, such as retrieving a price for the first product description containing a phrase. It is concise, returns a value from a parallel range, and can handle contains, starts-with, ends-with, and fixed-character patterns with the same function.
Use a different approach when the task is to return every matching row rather than only one. In Microsoft 365, the FILTER function can return multiple records that meet a condition, while XLOOKUP returns one match. For an exact lookup, leave wildcard characters out and use XLOOKUP’s default exact-match behavior; wildcard mode is not necessary. For case-sensitive text matching or more complicated rules, consider a formula built specifically for those requirements rather than expecting wildcard mode to add features it does not provide.
Wildcard searches can also be less precise than they first appear. Searching for *art*, for example, can find “Carton” as well as “Art Supplies,” because the pattern looks for those letters anywhere in the text, not a separate word. If word boundaries matter, use a more distinctive search phrase or a method that explicitly checks boundaries.
Frequently Asked Questions
Does XLOOKUP support wildcards?
Yes. Set the fifth argument, match_mode, to 2. Then asterisk, question-mark, and tilde wildcard rules can be used in the lookup value.
How do I make XLOOKUP find text containing a word?
Put an asterisk on both sides of the search text and use wildcard match mode. For example, with search text in D2 and the lookup values in A2:A20, use =XLOOKUP("*"&D2&"*",A2:A20,B2:B20,"Not found",2). It returns the corresponding value for the first match.
Can XLOOKUP return all wildcard matches?
No. XLOOKUP returns one result: the first match by default, or the last match when reverse search is specified. To return multiple matching records, use a function designed to spill multiple results, such as FILTER in Microsoft 365.
How do I search for an actual asterisk in a cell?
Prefix it with a tilde in the lookup pattern. For example, Item~* treats the asterisk as a literal character. Use ~? to search for a literal question mark.
Are XLOOKUP wildcard searches case-sensitive?
No. Wildcard matching in XLOOKUP is case-insensitive, so uppercase and lowercase versions of the same letters are treated as a match.
Why does my wildcard formula return the wrong row?
It may be finding a valid but earlier match, because XLOOKUP searches from top to bottom by default. Check for duplicate matches, make the pattern more specific, or use reverse search if the last matching row is the one you want.
Related
- Excel Shuffle List of Names: 10 Easy Methods
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Growth Analysis: Formulas, Trends, and ExamplesExcel growth analysis measures how a value changes over time. For two consecutive values in B2 and B3,…
- Median Formula Examples in Excel and by HandThe median is the middle value after a set of numbers is arranged from smallest to largest; with…
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the…
- Excel Formula for a Future Date: Add Days or MonthsIf the starting date is in A2 and the number of days to add is in B2, enter…
- Highlight Upcoming Dates in Excel with a Formula RuleTo highlight dates coming up in the next 30 days, select the date cells and create a formula-based…
- Excel CONCAT Formula: Combine Two Columns With a SpaceTo combine two columns in Excel, enter =CONCAT(A2," ",B2) in a blank cell, replacing A2 and B2 with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.