If you’ve ever wanted to perform a partial match in Excel — like finding any cell that contains the word “Smith” — you’re in luck. Excel’s VLOOKUP supports wildcards, letting you create dynamic, flexible searches that don’t require exact values.
Let’s break it down.
If you’ve ever wanted to perform a partial match in Excel — like finding any cell that contains the word “Smith” — you’re in luck. Excel’s VLOOKUP supports wildcards, letting you create dynamic, flexible searches that don’t require exact values.
Let’s break it down.
The Formula
=VLOOKUP(“*”&A1&”*”, B2:C100, 2, 0)
What It Does
This formula searches for a partial match using a wildcard and returns a corresponding value from the adjacent column.
Understanding the Components
“*”&A1&”*”
- This constructs a wildcard search string based on the value in cell A1.
- The asterisks * mean “match any number of characters before or after.”
- If A1 contains Smith, Excel will search for anything like:
- “John Smith”
- “Dr. Smithson”
- “TheSmithCompany”
B2:C100
- This is your lookup range.
- Column B (the first column) is where Excel will search for the match.
- Column C (the second column) is where Excel will return the result from.
2
- This tells Excel to return the value from the second column of your table range.
0 (or FALSE)
- This ensures exact match behavior — but it still works with wildcards like *Smith*.
- If no match is found, the result will be #N/A.
What Happens If You Use 1 Instead of 0?
=VLOOKUP(“*”&A1&”*”, B2:C100, 2, 1)
- 1 (or TRUE) tells Excel: “It’s okay to give me an approximate match.”
- But there’s a catch: approximate match doesn’t work with wildcards.
- Also, your lookup column must be sorted in ascending order for this to work properly.
Result: You’ll likely get unexpected results or errors. Stick with FALSE when using wildcards.
Performs a wildcard-enabled search (with the 2 at the end specifying wildcard match mode).
Final Thoughts
Wildcards are small but mighty tools in Excel — they let you search smarter, not harder. By combining them with VLOOKUP, COUNTIFS, or XLOOKUP, you can build robust formulas that adapt to real-world, messy data.
Try it out with your own data and see how much time you save!
What It Does
This formula searches for a partial match using a wildcard and returns a corresponding value from the adjacent column.
Understanding the Components
“*”&A1&”*”
- This constructs a wildcard search string based on the value in cell A1.
- The asterisks * mean “match any number of characters before or after.”
- If A1 contains Smith, Excel will search for anything like:
- “John Smith”
- “Dr. Smithson”
- “TheSmithCompany”
B2:C100
- This is your lookup range.
- Column B (the first column) is where Excel will search for the match.
- Column C (the second column) is where Excel will return the result from.
2
- This tells Excel to return the value from the second column of your table range.
0 (or FALSE)
- This ensures exact match behavior — but it still works with wildcards like *Smith*.
- If no match is found, the result will be #N/A.
What Happens If You Use 1 Instead of 0?
=VLOOKUP(“*”&A1&”*”, B2:C100, 2, 1)
- 1 (or TRUE) tells Excel: “It’s okay to give me an approximate match.”
- But there’s a catch: approximate match doesn’t work with wildcards.
- Also, your lookup column must be sorted in ascending order for this to work properly.
Result: You’ll likely get unexpected results or errors. Stick with FALSE when using wildcards.
Performs a wildcard-enabled search (with the 2 at the end specifying wildcard match mode).
Final Thoughts
Wildcards are small but mighty tools in Excel — they let you search smarter, not harder. By combining them with VLOOKUP, COUNTIFS, or XLOOKUP, you can build robust formulas that adapt to real-world, messy data.
Try it out with your own data and see how much time you save!