To use XLOOKUP for a partial match, set match_mode to 2 and include wildcard characters in the lookup value. This is the pattern to remember:
=XLOOKUP("*"&A2&"*",$E$2:$E$20,$F$2:$F$20,"Not found",2)
That tells Excel to search for text that contains the value in A2, not just an exact match.
If you are new to the function overall, read XLOOKUP Function in Excel: Step-by-Step Guide for Beginners first. This guide focuses on one specific problem: when your data is close, but not identical enough for a normal exact match.
When Wildcards Help
Wildcard matching is useful when:
- product descriptions contain extra words,
- item codes have prefixes or suffixes,
- names are only partially known,
- imported text is inconsistent.
For example, you may want to search for chair and match Office Chair - Black or Desk Chair Small.
That is very different from an exact-match lookup, where the cell contents need to line up precisely.
The Main Wildcard Characters
In Excel lookup formulas, the most useful wildcard characters are:
*for any sequence of characters?for a single character~to escape a literal wildcard character
With XLOOKUP, you activate wildcard behavior by setting match_mode to 2.
Contains Match Example
Suppose column E contains product descriptions and column F contains prices. If A2 contains the keyword you want to search, use:
=XLOOKUP("*"&A2&"*",$E$2:$E$20,$F$2:$F$20,"Not found",2)
This wraps the lookup value in asterisks, which means:
- any text before the keyword is allowed,
- any text after the keyword is allowed,
- the keyword can appear anywhere inside the matched text.
That makes the formula a good fit for "contains" searches.
Starts-With and Ends-With Matches
You are not limited to contains logic.
To match values that start with the text in A2:
=XLOOKUP(A2&"*",$E$2:$E$20,$F$2:$F$20,"Not found",2)
To match values that end with the text in A2:
=XLOOKUP("*"&A2,$E$2:$E$20,$F$2:$F$20,"Not found",2)
This gives you flexible lookup behavior without needing helper columns in simple cases.
Why Partial Match Can Still Fail
Wildcard matching solves one problem, but it does not solve every text issue.
Hidden spaces still matter
If imported text contains leading or trailing spaces, or repeated spaces inside the string, your lookup may still behave unpredictably. In those cases, cleanup functions such as TRIM can help stabilize the data first.
That is also why VLOOKUP Not Working with Text Values: 6 Fixes You Need remains relevant even when you are using XLOOKUP.
The first partial match wins
XLOOKUP returns the first matching result it finds. If several rows contain the same keyword, you may get a different row than you expected.
The pattern is too broad
Searching for pen may match Pencil, Pendulum, or Open Box Pen Set depending on your data. If that makes the result too loose, you may need a more specific lookup value or a multiple-criteria approach like How to Use XLOOKUP with Multiple Criteria in Excel.
Using Question Marks
The ? wildcard represents one character.
That can help when:
- a code is mostly fixed but one character varies,
- file names or IDs follow a strict pattern,
- you want a controlled partial match rather than a broad keyword search.
Example:
=XLOOKUP("AB?12",$E$2:$E$20,$F$2:$F$20,"Not found",2)
This will match a value like ABC12 or ABX12, as long as only one character varies in that position.
A Practical Rule for Beginners
Use wildcard XLOOKUP when:
- an exact match is too strict,
- you understand that the first valid partial match wins,
- your data is reasonably clean.
Do not use it blindly on messy imports where text inconsistencies may hide the real issue. In those cases, the better move is often cleanup first, then lookup second.
If you are already seeing #N/A, spill issues, or inconsistent results, go next to How to Fix Common XLOOKUP Errors in Excel.
Microsoft documents wildcard mode in the official XLOOKUP documentation. For a compact formula example built around contains logic, Exceljet's wildcard XLOOKUP example is a good external reference.
Verdict
Wildcard XLOOKUP is one of the fastest ways to handle partial text matches in Excel.
The key is to remember two things:
- set
match_modeto2, - control how broad the pattern is with
*and?.
Once you do that, XLOOKUP becomes much more useful for messy real-world text data.