Yes. In Microsoft Excel for Microsoft 365, XLOOKUP can find a partial text match when you set its match mode to 2 and use wildcard characters in the lookup value. For example, if the search text is in E2, this finds the first value in A2:A10 containing that text and returns the corresponding value from B2:B10: =XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"No match",2).
What wildcard XLOOKUP does
XLOOKUP normally looks for an exact match. Its optional fifth argument, match_mode, changes how it compares the lookup value with items in the lookup array. Set match_mode to 2 to enable wildcard matching. A wildcard is a character that stands in for other text, allowing a search to find a value even when you know only part of it.
The main wildcard characters are:
*matches any number of characters, including no characters. It is useful for finding text that starts with, ends with, or contains a phrase.?matches exactly one character. It helps when you know most of a word or code but not one position.~is the escape character. Put it before an asterisk or question mark when you want to search for that symbol as literal text, rather than use it as a wildcard.
Wildcard mode is for text-pattern matching, not a general “close enough” search. It does not correct spelling, measure similarity, or behave like a regular-expression search. It also does not turn XLOOKUP into a function that returns every matching row: XLOOKUP returns one result, normally the first match in the search order.
Set up the lookup formula
Use the lookup array for the values you want to search and the return array for the corresponding results. Those arrays should line up: the first item in the return array must be the result for the first item in the lookup array, and so on. In the example below, column A contains product names and column B contains product codes; E2 contains the search fragment.
With product names in A2:A10, product codes in B2:B10, and a fragment such as mug in E2, use:
=XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"No match",2)
The asterisks on either side mean “any characters before” and “any characters after,” so the formula looks for the fragment anywhere within a product name. The ampersand joins the wildcard characters to the contents of E2. If the first matching product is Blue mug and its code is M-101, the result is M-101. If no product name contains the search text, the formula displays No match.
The fifth argument matters: without the final 2, XLOOKUP uses its default exact-match mode, and the asterisks are not treated as wildcard instructions. The fourth argument, here No match, supplies a clear result when nothing is found. If you omit it, XLOOKUP returns an #N/A error for an unsuccessful search.
Examples: contains, begins with, and ends with
Change where the wildcard appears to control which part of the text is allowed to vary. These examples all assume that values to search are in A2:A10, corresponding results are in B2:B10, and the search term is in E2.
- Contains:
=XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"No match",2)finds the first cell containing the fragment anywhere. - Begins with:
=XLOOKUP(E2&"*",A2:A10,B2:B10,"No match",2)finds the first cell whose beginning matches E2. If E2 contains Blue, Blue mug qualifies but Travel mug does not. - Ends with:
=XLOOKUP("*"&E2,A2:A10,B2:B10,"No match",2)finds the first cell whose ending matches E2. For example, XL can find a name ending in XL.
For a pattern with one unknown character, use a question mark. With codes in A2:A10, corresponding descriptions in B2:B10, and the pattern AB?7 in E2, enter =XLOOKUP(E2,A2:A10,B2:B10,"No match",2). It can match ABX7 or AB17, because the question mark stands for one character. It will not match ABXX7, which has two characters in that position. Asterisks and question marks can be combined when the pattern requires both flexible sections and fixed characters.
Wildcard lookup is case-insensitive, like ordinary XLOOKUP matching: a search for mug can match Mug. If capitalization is essential, wildcard XLOOKUP alone does not enforce it; a case-sensitive approach requires a different formula design.
Search order and duplicate matches
By default, XLOOKUP searches from the first item to the last and returns the first match it encounters. If several product names contain mug, the result is the code for the earliest qualifying name in the lookup array. This is not necessarily the shortest name, the closest-looking match, or the most recently added record.
To return the last matching item instead, use the optional sixth argument, search_mode, with -1. With product names in A2:A10, codes in B2:B10, and the fragment in E2, this searches from the bottom upward:
=XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"No match",2,-1)
That changes which duplicate wins; it does not return all matches. If you need a list of every row that contains a fragment, consider FILTER, available in Microsoft 365 and Excel 2021 or later. The search order also does not sort the data: it simply determines the direction in which XLOOKUP checks the lookup array.
Searching for literal asterisks and question marks
Because * and ? have special meanings in wildcard mode, searching for those symbols themselves requires the tilde escape. For example, suppose A2:A10 contains text, B2:B10 contains results, and you want the first cell containing the literal text A*B. This lookup pattern means any text, followed by a literal asterisk, followed by any text:
=XLOOKUP("*A~*B*",A2:A10,B2:B10,"No match",2)
The tilde before the asterisk makes that asterisk literal; the first and last asterisks remain wildcards. Use ~? to search for a literal question mark and ~~ to search for a literal tilde.
If the search fragment comes from a cell and might itself contain wildcard symbols, escape the fragment before adding the outer wildcards. With the fragment in E2 and the lookup and return ranges in A2:A10 and B2:B10, use:
=XLOOKUP("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"~","~~"),"*","~*"),"?","~?")&"*",A2:A10,B2:B10,"No match",2)
The substitutions escape tildes first, then asterisks and question marks. That order prevents an added escape tilde from being escaped again. This version is useful in a search box where users may type punctuation and expect it to be treated literally.
Prevent blank searches and troubleshoot errors
A blank search fragment can be surprising. In a contains formula, joining an empty E2 to two asterisks creates a pattern that can match text broadly, so the formula may return a result even though nobody entered a search term. If E2 is the input, A2:A10 the values, and B2:B10 the results, this version asks for a term before running the lookup:
=IF(E2="","Enter search text",XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"No match",2))
When a wildcard formula does not behave as expected, check these common causes:
- Missing match mode: Confirm that the fifth argument is 2. Putting 2 in the fourth position changes the if_not_found argument instead.
- Misplaced wildcards: Two wildcards around the term search anywhere; one after it searches for a beginning; one before it searches for an ending.
- Unexpected first result: Check for an earlier matching value or duplicates. Reorder the data or use reverse search if the last match is the one you want.
- Inconsistent ranges: Make sure the lookup and return arrays have compatible dimensions and corresponding rows. A lookup array of ten cells paired with a differently sized return range can cause an error.
- Numbers stored as text or numeric values: Wildcard matching is intended for text patterns. If the target is a number, use an exact numeric lookup or convert and format the data deliberately rather than assuming a wildcard will search its digits as text.
- Spaces or hidden characters: A leading, trailing, or nonbreaking space may make a value different from what it appears to be. Inspect or clean the source data when a visually obvious match is missed.
- Unsupported Excel version: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not as a built-in function in Excel 2019 or Excel 2016. An older version may show a function-name error or an _xlfn prefix.
How wildcard XLOOKUP compares with alternatives
XLOOKUP is convenient when you need one result from a matching row: its lookup and return arrays are separate arguments, it can return a custom not-found message, and it searches first-to-last by default. VLOOKUP can also use wildcard matching with an appropriate match setting, but it requires a table range and a column index, and the return column must be to the right of the lookup column.
For all matching rows rather than one, FILTER is usually a more suitable Microsoft 365 or Excel 2021 option. For older Excel versions, users often build solutions with INDEX and MATCH, or use a helper column to identify partial matches. Those alternatives have different formula requirements and do not make XLOOKUP available in versions that lack it.
Wildcard search is also different from approximate match. XLOOKUP’s approximate modes look for the next smaller or larger item in an ordered lookup array; wildcard mode looks for a text pattern. A wildcard formula is not a fuzzy search for misspelled words or similar spellings.
Frequently Asked Questions
Why does XLOOKUP ignore my asterisk?
Check that the fifth argument is 2. Wildcards are recognized in XLOOKUP’s wildcard match mode; without it, the search may treat the asterisk as an ordinary character.
Can XLOOKUP find text in the middle of a cell?
Yes. Put an asterisk before and after the search fragment and use match_mode 2. The pattern can then match text before and after the fragment.
Does wildcard XLOOKUP return every match?
No. XLOOKUP returns one result: the first match in its search direction. Use FILTER in a supported Excel version if you need all rows that satisfy a condition.
Is wildcard XLOOKUP case-sensitive?
No. XLOOKUP’s text matching is case-insensitive. If uppercase and lowercase letters must be distinguished, use a case-sensitive formula approach instead.
Can I use wildcard XLOOKUP in Excel 2019?
Excel 2019 does not include XLOOKUP as a built-in function. Use Microsoft 365 or Excel 2021 or later, or choose a lookup method supported by your version.
What does the tilde do in an XLOOKUP pattern?
It escapes the next wildcard symbol. For example, ~* searches for a literal asterisk, while an unescaped asterisk matches any number of characters.
Related
- Excel Text Box Wrap Text: How to Enable It
- Excel Template for a Social Media Calendar
- Excel Job List Template: Track Applications and Follow-Ups
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort an Excel Column Highest to Lowest: Quick StepsTo sort numbers in Excel from highest to lowest, select a cell in the column and choose Data…
- How to Enable Sort and Filter in Excel: Step by StepTo enable sorting and filtering in Excel for Microsoft 365 on Windows, click a cell in your data…
- Excel Timesheet Formulas for Overtime, Breaks, and PayFor a basic Excel timesheet, put a shift’s start time in B2, end time in C2, and unpaid…
- Excel Freeze Panes: Keep Top Row and Left Column VisibleTo freeze the top row and left column together in Excel for Microsoft 365 on Windows, select cell…
- Excel FILTER Formula for Multiple Criteria and ResultsTo filter rows by multiple criteria in Excel, use the FILTER function and combine conditions with multiplication for…
- CONCAT in Excel: Add a Space Between Text ValuesTo add a space when joining text in Excel, include a space in quotation marks as an argument:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.