VLOOKUP with wildcards
Wrap the lookup value in asterisks and force an exact match: =VLOOKUP("*"&D2&"*",A:B,2,FALSE). The * stands for any number of characters and ? for exactly one. Wildcards are ignored unless the fourth argument is FALSE, which is the reason most wildcard VLOOKUPs silently fail.
Find the row whose key contains D2
=VLOOKUP("*"&D2&"*",A:B,2,FALSE)FALSE is not optional here — with TRUE or omitted, the asterisks are treated as literal characters.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
The two wildcards
* matches any run of characters, including none. ? matches exactly one character. They can appear anywhere in the lookup value and can be combined.
VLOOKUP returns the first row that matches, scanning top to bottom. With a wildcard that is frequently not the row you meant — if several keys contain "north", you get whichever sits highest. Sort deliberately, or use a formula that can search from the bottom.
Starts with
=VLOOKUP(D2&"*",A:B,2,FALSE)Ends with
=VLOOKUP("*"&D2,A:B,2,FALSE)Contains
=VLOOKUP("*"&D2&"*",A:B,2,FALSE)Fixed-length pattern
=VLOOKUP("A??-2026",A:B,2,FALSE)Matches A01-2026 and A99-2026 but not A1-2026.
Correct, but depends on values from your own sheet, so we cannot run it here.
Why the wildcard VLOOKUP is not working
The fourth argument is the answer four times out of five. Omitting it, or passing TRUE, puts VLOOKUP in approximate-match mode, where wildcards are inert and the asterisks are matched as literal asterisk characters. It will not error — it will return #N/A or, worse, a plausible wrong row.
The second cause is that the lookup column is not the first column of the range. VLOOKUP always searches the leftmost column of whatever range you give it, so A:B searches column A. If your key is in column B, either move it or use INDEX/MATCH.
The third is invisible characters. A trailing space in either the lookup value or the data makes an exact match fail while looking identical on screen. TRIM both sides.
Finally, numbers stored as text will not match real numbers, and wildcards cannot help — they apply to text only.
Guard against stray spaces
=VLOOKUP("*"&TRIM(D2)&"*",A:B,2,FALSE)Key is not in the leftmost column
=INDEX(A:A,MATCH("*"&D2&"*",B:B,0))MATCH supports the same wildcards and does not care about column order.
Correct, but depends on values from your own sheet, so we cannot run it here.
Readable message instead of #N/A
=IFERROR(VLOOKUP("*"&D2&"*",A:B,2,FALSE),"No match")Matching a literal asterisk or question mark
When the data genuinely contains * or ?, escape it with a tilde so it is treated as an ordinary character. Without the tilde, searching for "SKU*" matches everything beginning with SKU rather than the one product literally called "SKU*".
Literal asterisk
=VLOOKUP("SKU~*",A:B,2,FALSE)Correct, but depends on values from your own sheet, so we cannot run it here.
Escape whatever the user typed
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),A:B,2,FALSE)Escape the tilde first, or you double-escape the ones you just added.
The modern alternatives
XLOOKUP takes wildcards through an explicit match mode rather than by inference, which makes the intent visible in the formula. It also searches in either direction and returns a fallback without an IFERROR wrapper.
In Google Sheets, REGEXMATCH inside a FILTER gives genuine pattern matching, which is worth reaching for once the requirement outgrows * and ?.
XLOOKUP with wildcards
=XLOOKUP("*"&D2&"*",A:A,B:B,"No match",2)Match mode 2 enables wildcards. The fourth argument replaces IFERROR.
Last match rather than first
=XLOOKUP("*"&D2&"*",A:A,B:B,"No match",2,-1)Regex matching in Google Sheets
=FILTER(B:B,REGEXMATCH(A:A,"north|south"))Google Sheets only.
Wildcard support by function
| Function | Wildcards work when | Notes |
|---|---|---|
| VLOOKUP | Fourth argument is FALSE | Returns the first match only |
| HLOOKUP | Fourth argument is FALSE | Same rules, searching across a row |
| MATCH | Third argument is 0 | Not restricted to the leftmost column |
| XLOOKUP | match_mode is 2 | Can also search last-to-first |
| XMATCH | match_mode is 2 | Returns a position |
| COUNTIF / SUMIF | Always | Wildcards are on by default |
| FILTER | Never | Use SEARCH or REGEXMATCH inside it |
Frequently asked
How do I use a wildcard in VLOOKUP?
Concatenate the wildcard onto the lookup value and set the fourth argument to FALSE: =VLOOKUP("*"&D2&"*",A:B,2,FALSE). * matches any run of characters, ? matches exactly one.
Why is my wildcard VLOOKUP returning #N/A?
Most often the fourth argument is TRUE or omitted, which disables wildcards. Other causes are the key not being in the leftmost column of the range, trailing spaces, or numbers stored as text.
How do I look up a value that actually contains an asterisk?
Escape it with a tilde: =VLOOKUP("SKU~*",A:B,2,FALSE). The tilde tells the engine to treat the next character literally.
Can VLOOKUP return the last match instead of the first?
No — VLOOKUP always returns the first match scanning downward. Use =XLOOKUP("*"&D2&"*",A:A,B:B,"No match",2,-1), where the -1 search mode scans from the bottom.
Do VLOOKUP wildcards work in Google Sheets?
Yes, identically, with the same FALSE requirement. Sheets additionally offers REGEXMATCH for patterns beyond * and ?.
Stop writing these by hand
Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.
Related
Last reviewed 2026-08-23. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.