# How to look up a partial match with wildcards in Excel

Wrap the search text in asterisks and turn on wildcard matching: `=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2)`.

**Excel:** `=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,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 | F |
| --- | --- | --- | --- | --- | --- |
| Company | Account manager | Annual spend |  | Search | north |
| Contoso Pharmaceuticals | Lena Park | 184000 |  | Annual spend |  |
| Fabrikam Industries | Omar Ruiz | 96500 |  |  |  |
| Northwind Traders | Priya Shah | 241300 |  |  |  |
| Adventure Works Cycles | Tom Becker | 72800 |  |  |  |
| Tailspin Toys | Ines Lopez | 55400 |  |  |  |
| Wide World Importers | Lena Park | 133900 |  |  |  |
| Litware Inc. | Omar Ruiz | 61200 |  |  |  |

Formula in F2: `=XLOOKUP("*"&F1&"*",A2:A8,C2:C8,,2)`

Result: $241,300

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

## Other ways

- `=VLOOKUP("*"&F1&"*",A2:C8,3,FALSE)`: VLOOKUP with FALSE accepts wildcards without any extra setting. Works in every version.
- `=INDEX(C2:C8,MATCH("*"&F1&"*",A2:A8,0))`: 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*".

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

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/partial-match-lookup-with-wildcards