Why don't wildcards work in XLOOKUP?
Formula (Excel and Google Sheets)
=XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)XLOOKUP treats * and ? as plain characters unless you set match mode 2: =XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2).
- Result on the example
- Noor Haddad in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
XLOOKUP's fifth argument is the match mode, and only 2 turns on wildcards. With it, "*Northwind*" matches any name that contains Northwind, so Northwind Traders is found. Without it, XLOOKUP looks for a company literally named "*Northwind*" and returns "Not found".
=XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)Result"Noor Haddad"
E1"Northwind"The value in E1.
"*"&E1"*Northwind"The & operator joins as text.
"*"&E1&"*""*Northwind*"The & operator joins as text.
A2:A76 rows × 1 col: "Contoso Pharmaceuticals", "Fabrikam Inc", "Northwind Traders", "Litware Software", …A range: 6 cells in one column.
B2:B76 rows × 1 col: "Lena Park", "Omar Ruiz", "Noor Haddad", "Tom Becker", …A range: 6 cells in one column.
XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)"Noor Haddad"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
"*"&E1&"*""*Northwind*" - lookup_array
A2:A76 rows × 1 col: "Contoso Pharmaceuticals", "Fabrikam Inc", "Northwind Traders", "Litware Software", … - return_array
B2:B76 rows × 1 col: "Lena Park", "Omar Ruiz", "Noor Haddad", "Tom Becker", … - if_not_found
"Not found" - match_mode
2
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2). We ran it in Google Sheets mode on the same data and it returns Noor Haddad.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP("*"&E1&"*",A2:B7,2,FALSE) | Noor Haddad | VLOOKUP accepts wildcards in exact-match mode without any extra setting. |
| =INDEX(B2:B7,MATCH("*"&E1&"*",A2:A7,0)) | Noor Haddad | MATCH with 0 takes wildcards too. |
Common mistakes
- A wildcard lookup returns the first match only. "*Soft*" stops at the first company containing it.
- To look for a real * or ?, put a tilde in front of it: "~*".
- Wildcards work on text only, not on numbers.
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.
Why don't wildcards work in XLOOKUP
E2, return the account manager for the first company whose name contains the text in E1.Questions
Does XLOOKUP in Google Sheets support wildcards?
Yes, with the same match mode 2.
How do I return every match?
Use FILTER with SEARCH: =FILTER(B2:B7,ISNUMBER(SEARCH(E1,A2:A7))).
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.