# How to look up a value that contains an asterisk or question mark

Use XLOOKUP, which treats * and ? as ordinary characters by default: `=XLOOKUP(F1,A2:A6,C2:C6)`. VLOOKUP and MATCH read them as wildcards.

**Excel:** `=XLOOKUP(F1,A2:A6,C2:C6)` (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 |
| --- | --- | --- | --- | --- | --- |
| Code | Description | Price |  | Code | TX*100 |
| TX-100 | Cable tie, 100 mm | 4.2 |  | Price |  |
| TX*100 | Cable tie, heavy duty | 6.8 |  |  |  |
| TX/100 | Cable tie, UV black | 5.1 |  |  |  |
| TX-200 | Cable tie, 200 mm | 5.6 |  |  |  |
| TX?200 | Cable tie assortment | 9.4 |  |  |  |

Formula in F2: `=XLOOKUP(F1,A2:A6,C2:C6)`

Result: $6.80

## How it works

In VLOOKUP, MATCH and COUNTIF, an asterisk means "any characters", so "TX*100" also matches TX-100, the first row, and returns the wrong price. XLOOKUP only uses wildcards when you ask for match mode 2; by default it compares the text literally and finds TX*100 itself, $6.80.

## Other ways

- `=VLOOKUP(SUBSTITUTE(SUBSTITUTE(F1,"*","~*"),"?","~?"),A2:C6,3,FALSE)`: For VLOOKUP: a tilde before * or ? makes it a literal character. Works in every version.
- `=VLOOKUP(F1,A2:C6,3,FALSE)`: Shown for contrast: the asterisk acts as a wildcard. (Returns $4.20, the price of TX-100.)

## Common mistakes

- The tilde itself is special too: a code containing ~ needs `"~~"` in VLOOKUP.
- COUNTIF and SUMIF also treat * and ? as wildcards, so they miscount codes like these. Escape them with ~ there too.

## Questions

**Does Google Sheets treat * as a wildcard in VLOOKUP?**

Yes, VLOOKUP and MATCH in Google Sheets accept wildcards and the ~ escape too.

**How do I search for codes that contain an asterisk?**

Escape it inside the pattern: `=COUNTIF(A2:A6,"*~**")` counts codes with a * anywhere.

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/lookup-value-with-asterisk