How to look up a value that contains an asterisk or question mark
Formula (Excel and Google Sheets)
=XLOOKUP(F1,A2:A6,C2:C6)Use XLOOKUP, which treats * and ? as ordinary characters by default: =XLOOKUP(F1,A2:A6,C2:C6). VLOOKUP and MATCH read them as wildcards.
- Result on the example
- $6.80 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
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.
=XLOOKUP(F1,A2:A6,C2:C6)Result6.8
F1"TX*100"The value in F1.
A2:A65 rows × 1 col: "TX-100", "TX*100", "TX/100", "TX-200", …A range: 5 cells in one column.
C2:C65 rows × 1 col: $4.20, $6.80, $5.10, $5.60, …A range: 5 cells in one column.
XLOOKUP(F1,A2:A6,C2:C6)6.8XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
F1"TX*100" - lookup_array
A2:A65 rows × 1 col: "TX-100", "TX*100", "TX/100", "TX-200", … - return_array
C2:C65 rows × 1 col: $4.20, $6.80, $5.10, $5.60, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(F1,A2:A6,C2:C6). We ran it in Google Sheets mode on the same data and it returns $6.80.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(SUBSTITUTE(SUBSTITUTE(F1,"*","~*"),"?","~?"),A2:C6,3,FALSE) | $6.80 | For VLOOKUP: a tilde before * or ? makes it a literal character. Works in every version. |
| =VLOOKUP(F1,A2:C6,3,FALSE) | $4.20Different result | 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.
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 value that contains an asterisk or question mark
F2, return the price for the exact code in F1.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.
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.