# Why don't wildcards work in XLOOKUP?

XLOOKUP treats * and ? as plain characters unless you set match mode 2: =XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2).

**Excel:** `=XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Company | Account manager |  | Search | Northwind |
| Contoso Pharmaceuticals | Lena Park |  | Manager |  |
| Fabrikam Inc | Omar Ruiz |  |  |  |
| Northwind Traders | Noor Haddad |  |  |  |
| Litware Software | Tom Becker |  |  |  |
| Adatum Corporation | Priya Shah |  |  |  |
| Tailspin Toys | Ines Lopez |  |  |  |

Formula in E2: `=XLOOKUP("*"&E1&"*",A2:A7,B2:B7,"Not found",2)`

Result: Noor Haddad

## 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".

## Other ways

- `=VLOOKUP("*"&E1&"*",A2:B7,2,FALSE)`: VLOOKUP accepts wildcards in exact-match mode without any extra setting.
- `=INDEX(B2:B7,MATCH("*"&E1&"*",A2:A7,0))`: 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.

## 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)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/xlookup-wildcards-not-working