How to look up a partial match with wildcards in Excel
Formula (Excel and Google Sheets)
=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2)Wrap the search text in asterisks and turn on wildcard matching: =XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2).
- Result on the example
- $241,300 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
An asterisk stands for any number of characters, so "*north*" matches "Northwind Traders". XLOOKUP only treats * and ? as wildcards when the match mode, the 5th argument, is 2. Matching ignores case.
=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2)Result241300
F1"north"The value in F1.
"*"&F1"*north"The & operator joins as text.
"*"&F1&"*""*north*"The & operator joins as text.
A2:A87 rows × 1 col: "Contoso Pharmaceuticals", "Fabrikam Industries", "Northwind Traders", "Adventure Works Cycles", …A range: 7 cells in one column.
C2:C87 rows × 1 col: $184,000, $96,500, $241,300, $72,800, …A range: 7 cells in one column.
XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2)241300XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
"*"&F1&"*""*north*" - lookup_array
A2:A87 rows × 1 col: "Contoso Pharmaceuticals", "Fabrikam Industries", "Northwind Traders", "Adventure Works Cycles", … - return_array
C2:C87 rows × 1 col: $184,000, $96,500, $241,300, $72,800, … - if_not_found
(omitted) - match_mode
2
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2). We ran it in Google Sheets mode on the same data and it returns $241,300.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP("*"&F1&"*",A2:C8,3,FALSE) | $241,300 | VLOOKUP with FALSE accepts wildcards without any extra setting. Works in every version. |
| =INDEX(C2:C8,MATCH("*"&F1&"*",A2:A8,0)) | $241,300 | MATCH with 0 also accepts wildcards. |
Common mistakes
- Without match mode 2, XLOOKUP looks for the literal text "*north*" and returns #N/A.
- Short search text matches more than you expect: "in" matches Fabrikam Industries, Northwind Traders, Tailspin Toys and Litware Inc., and the first one wins.
- Wildcards only work on text. A number stored as a number won't match "*42*".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a partial match with wildcards
F2, return the annual spend of the first company whose name contains the text in F1.Questions
How do I match names that start with the text?
Put the asterisk only at the end: F1&"*".
How do I return every partial match?
Use FILTER with SEARCH: =FILTER(A2:A8,ISNUMBER(SEARCH(F1,A2:A8))).
What does the ? wildcard do?
It stands for exactly one character: "Gr?y" matches Gray and Grey.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.