VLOOKUP supports wildcards for powerful partial-matching: use the asterisk (*) for “any number of characters”, the question mark (?) for “exactly one character”, and the tilde (~) to treat wildcards literally, enabling flexible lookups when exact keys don’t exist or vary in format.
Table of Contents
Wildcard cheatsheet
- Asterisk (*) = match zero or more characters (e.g., “Pro*” finds “Pro”, “ProMax”, “Pro 3000”).
- Question mark (?) = match exactly one character (e.g., “X?A” matches X1A, X2A).
- Tilde (~) = escape wildcards to search for literal * or ? (e.g., “Price~*Discount”).
Core patterns with formulas
- Contains text anywhere: =VLOOKUP(“*”&H2&”*”, $A$2:$C$100, 3, FALSE) returns first row containing the string in H2.
- Starts with string: =VLOOKUP(H2&”*”, $A$2:$C$100, 2, FALSE) for prefix matches like “John*”.
- Ends with string: =VLOOKUP(“*”&H2, $A$2:$C$100, 2, FALSE) for suffix matches like “*son”.
- Single-character variant: =VLOOKUP(“X?A”, $A$2:$B$100, 2, FALSE) to allow exactly one character in the middle.
- Escaped literal asterisk: =VLOOKUP(“Price~*Discount”, $A$2:$B$100, 2, FALSE) to match a real asterisk.
Step-by-step examples
1) Partial product name (contains)
Scenario: Return price where Product column contains the term in H2.
Formula: =VLOOKUP(“*”&H2&”*”, $A$2:$B$200, 2, FALSE)
Why: Concatenate wildcards around the search term to match anywhere in the text.
2) Starts-with search for names
Scenario: Names column A has “John Carter”, “Johnny Appleseed”, etc.
Formula: =VLOOKUP(H2&”*”, $A$2:$C$200, 3, FALSE)
Why: A trailing asterisk matches any suffix after the prefix.
3) Fixed pattern codes with one unknown
Scenario: Item codes like X1A, X2A, X3B; vary at the middle digit/letter.
Formula: =VLOOKUP(“X?A”, $A$2:$B$200, 2, FALSE)
Why: Question mark replaces exactly one character.
4) Literal wildcard in text
Scenario: Labels like “Price*Discount”.
Formula: =VLOOKUP(“Price~*Discount”, $A$2:$B$200, 2, FALSE)
Why: Tilde escapes the asterisk so it’s treated literally.
Common pitfalls and fixes
- Use exact match mode: Set the last argument to FALSE; wildcard logic requires exact-match search mode to behave predictably.
- Lookup column must be first: VLOOKUP only matches against the leftmost column of the table_array; rearrange or use INDEX/MATCH/XLOOKUP if needed.
- First match only: VLOOKUP returns the first row that matches; consider FILTER or INDEX with SMALL for subsequent matches.
- Hidden spaces/cleaning: Clean data with TRIM/CLEAN or standardize case to prevent #N/A from unseen characters.
- Wildcard placement: Misplaced wildcards or wrong column index commonly break formulas; verify “*” and “?” are inside quotes and concatenated with & where needed.
When to use INDEX/MATCH or XLOOKUP instead
Return from a column to the left or dynamic column positions:
- =INDEX(return_col, MATCH(“*”&H2&”*”, lookup_col, 0)) offers partial match without reordering columns.
- Multiple criteria with partial match: XLOOKUP in Microsoft 365 can handle array conditions more cleanly than nested VLOOKUP.
- Retrieve all matches, not just the first: Use FILTER (365): =FILTER(B2:B200, ISNUMBER(SEARCH(H2, A2:A200))) for all partial matches instead of only the first.
Performance and scaling
- Limit the table range: Large table_arrays slow wildcard scans; restrict to necessary rows/columns for speed.
- Helper searches: Precompute SEARCH/ISNUMBER flags in a helper column to filter candidates and speed lookups on big sheets.
- Prefer XLOOKUP: On large models, XLOOKUP with arrays is often more readable and performant than nested VLOOKUPs.
Troubleshooting checklist
- Is [range_lookup] FALSE? If not, set to FALSE.
- Is the lookup column the first column in the table_array? If not, switch to INDEX/MATCH or reorder.
- Are there extra spaces? Try TRIM on both lookup and data columns.
- Are wildcards quoted and concatenated correctly? Use “*”&cell&”*” or “X?A”.
- Are multiple matches expected? Consider FILTER or add a second criterion to narrow results.
