XLOOKUP Partial Match: Wildcard Examples in Excel

For a contains-style partial match, put the text to find in E2 and use =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2). The final 2 enables wildcard matching, and the formula returns the value from column B for the first matching entry in A2:A7.

How XLOOKUP partial matching works

XLOOKUP looks for a value in one range and returns a corresponding value from another range. By default, it looks for an exact match. That means a lookup value of Northwind will not normally match Northwind Traders. To find text that appears within a longer entry, use wildcard characters in the lookup value and set the match mode to 2.

The syntax is XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The fifth argument is match_mode. Its value of 2 tells Excel to interpret asterisk and question mark characters as wildcards. The fourth argument is optional, but it is useful to provide a readable result when there is no match.

In the opening formula, the ampersands join an asterisk, the text in E2, and another asterisk. An asterisk can stand for any number of characters, including no characters, so the pattern finds the search text anywhere in a cell. XLOOKUP searches from top to bottom by default and returns the first match it encounters.

Set up a practical example

Suppose A2:A7 contains company names and B2:B7 contains sales totals. The rows are Northwind Traders — 14200, Southridge Supplies — 9800, Northwind Services — 17600, East Bay Office — 6300, Northwind Retail — 11200, and Westfield Parts — 5400. Enter Northwind in E2. With that layout, the formula from the opening paragraph returns 14200, the value on the first matching row, Northwind Traders.

Here is what each argument contributes:

"*"&E2&"*" builds a pattern that can contain any characters before and after the search text.
A2:A7 is the range Excel searches.
B2:B7 is the aligned range that supplies the result.
"No match" is displayed if there is no qualifying entry.
2 turns on wildcard matching.

The lookup and return ranges need to correspond row by row and have compatible dimensions. If A2 is the match, Excel returns the value in B2; it does not search B2:B7 for a separate match. This alignment is important when adapting an example to a real worksheet.

Match text at the beginning or end

Moving or removing an asterisk changes where the searched text is allowed to appear. With the same A2:A7 and B2:B7 layout, and Northwind in E2, use =XLOOKUP(E2&"*",A2:A7,B2:B7,"No match",2) to match an entry that starts with Northwind. It returns 14200 because Northwind Traders is the first such entry.

To find an entry that ends with the search text, put Services in E2 and, with the same ranges, use =XLOOKUP("*"&E2,A2:A7,B2:B7,"No match",2). The result is 17600, from Northwind Services. In that pattern, anything can come before Services, but no characters can follow it.

These are still text-pattern searches, not fuzzy matching. For example, they do not automatically treat a misspelling such as Northwnd as equivalent to Northwind, and they do not measure how similar two names are. They look for the literal search text in the positions allowed by the wildcard pattern.

Use question marks and other wildcard details

The asterisk matches a sequence of any length. A question mark matches exactly one character. For example, if A2:A7 contains product codes and B2:B7 contains descriptions, the pattern A?7 can match codes such as AB7 or AX7, but not A237. With the code range in A2:A7, descriptions in B2:B7, and the pattern entered in E2, use =XLOOKUP(E2,A2:A7,B2:B7,"No match",2). It returns the description for the first code matching that pattern. The actual returned text depends on the data in the worksheet.

If a search value itself contains an asterisk or question mark that should be treated literally, precede that character with a tilde in the lookup pattern. For instance, ~* represents a literal asterisk rather than the wildcard. This distinction matters when searching file names, product descriptions, or other text that can contain wildcard characters.

Wildcard XLOOKUP searches are generally case-insensitive, as are ordinary XLOOKUP text comparisons. A search for northwind can therefore match Northwind. If capitalization must distinguish otherwise identical text, use a case-sensitive test rather than relying on wildcard match mode.

Return the last match or all matching rows

When more than one entry contains the search text, standard XLOOKUP returns only one result: the first match in the lookup range. You can change the search direction with the sixth argument. With the company names in A2:A7, sales in B2:B7, and Northwind in E2, =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2,-1) searches from the bottom upward and returns 5400? No: in the listed example Northwind Retail is in row 6, so the result is 11200. The -1 search mode means search last to first; it does not change wildcard matching.

If you want every matching row rather than only the first or last one, FILTER is often a better fit. With company names in A2:A7, sales in B2:B7, and Northwind in E2, =FILTER(A2:B7,ISNUMBER(SEARCH(E2,A2:A7)),"No match") returns the three matching rows: Northwind Traders with 14200, Northwind Services with 17600, and Northwind Retail with 11200. FILTER is available in Microsoft 365 and Excel 2021 or later. Unlike the XLOOKUP wildcard approach, this formula uses SEARCH to identify text contained anywhere in each name.

Case-sensitive partial matches

If capitalization matters, FIND can test for text inside each cell with case sensitivity. With company names in A2:A7, sales in B2:B7, and Services in E2, use =XLOOKUP(TRUE,ISNUMBER(FIND(E2,A2:A7)),B2:B7,"No match"). It returns 17600 for Northwind Services. FIND returns a position when it locates the exact capitalization and an error when it does not; ISNUMBER converts those outcomes to TRUE or FALSE, which XLOOKUP can search. In Microsoft 365 and Excel 2021 or later, the array calculation is supported directly.

This version finds the first case-sensitive occurrence, not every one. It also uses substring matching rather than wildcard match mode, so wildcard characters typed in E2 are treated as ordinary search text by FIND. If the formula returns an unexpected result, check whether the case-sensitive behavior is actually needed before using this more complex approach.

Common problems and how to fix them

Forgetting match mode 2: A pattern containing asterisks will not behave as intended if the fifth argument is omitted. XLOOKUP defaults to exact matching, so specify 2.
Getting No match unexpectedly: Check extra spaces, spelling, and whether E2 is blank. A blank search value combined with surrounding asterisks can create a pattern that matches broadly, so guard against empty input if that is not desired.
Getting an unexpected first result: XLOOKUP returns the first matching row in its default search direction. Review the order of the lookup range, or use search mode -1 if the last match is wanted.
Searching numbers with wildcards: Wildcard matching is intended for text. A numeric cell is not necessarily matched as if its displayed digits were text. For partial searches on IDs, store the IDs as text or create a text helper column; changing a number’s display format alone does not convert its underlying value to text.
Ranges do not line up: Use lookup and return ranges with the same number of rows (or compatible dimensions). Otherwise the returned value may not correspond to the record you intended, or Excel may report an error.
Formula separators differ: The examples use commas, as in standard English-language Excel formulas. Some regional settings require semicolons as argument separators, so use the separator configured for your Excel installation if Excel rejects an otherwise correct formula.

XLOOKUP compared with other options

For one result, XLOOKUP is a convenient alternative to older VLOOKUP formulas: the lookup range and return range are named separately, and the return range can be to the left or right of the lookup range. VLOOKUP can also use wildcards for text searches, but it requires a table range and a column index number. Both functions return a single matching result unless used within a broader formula.

FILTER is preferable when the goal is a list of all rows containing a phrase. SEARCH is case-insensitive and can be combined with ISNUMBER to form a contains test; FIND provides a case-sensitive test. These approaches can be more flexible than wildcard matching, but they may take more explaining and require a version of Excel that supports dynamic arrays for results that spill into neighboring cells.

For fuzzy matching—finding likely misspellings or near-duplicates—neither wildcard XLOOKUP nor SEARCH is enough by itself. Excel’s Power Query has fuzzy merge options for comparing text between tables, but those settings and results differ from a direct worksheet lookup. Choose the method based on whether the need is a literal substring, a case-sensitive substring, several matching rows, or approximate similarity.

Excel version and platform notes

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Windows, Mac, and the web. The examples here use Microsoft Excel for Microsoft 365 on Windows. The formulas work similarly in current Mac and web versions, although the interface for editing or filling formulas can vary. If a workbook is opened in an older Excel version that does not support XLOOKUP, the function may display as an unsupported-function error.

In desktop Excel, enter the formula in a cell and press Enter. If the formula should be copied down a column, consider whether the ranges should remain fixed; for example, absolute references such as $A$2:$A$7 and $B$2:$B$7 keep the ranges from shifting when copied. Do not add dollar signs when the formula will only be used once, or when changing ranges during copying is intentional.

Frequently Asked Questions

What is the XLOOKUP formula for contains partial match?
Use an asterisk before and after the search text and set match_mode to 2. For text in E2, a lookup range in A2:A7, and results in B2:B7, the pattern is =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2).

Can XLOOKUP match text that starts with a word?
Yes. Put the wildcard after the search text, as in Northwind followed by an asterisk, and use match_mode 2. That matches entries beginning with Northwind, rather than entries where the word appears anywhere.

Does XLOOKUP partial match return all results?
No. XLOOKUP returns one result: the first match by default, or the last if its search mode is set to -1. Use FILTER with a text search test when you need every matching row.

Is XLOOKUP partial match case-sensitive?
No. Wildcard matching in XLOOKUP is case-insensitive. For a case-sensitive contains search, use FIND with ISNUMBER as the lookup test, or another case-sensitive method.

Why does my XLOOKUP wildcard formula return no match?
Check that the formula includes match_mode 2, that the search text is present in the cells, and that the lookup values are text. Wildcard patterns may not work as expected against numeric values, even when those numbers are displayed as digits.

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.