Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to do a lookup with wildcards in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=VLOOKUP("*"&D2&"*",$A$2:$B$5,2,0)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Product | Price |
| Blue Widget | 29.99 |
| Red Gadget | 49.99 |
| Green Doohickey | 14.99 |
| Blue Thingamajig | 39.99 |
=VLOOKUP("*"&D2&"*",$A$2:$B$5,2,0)→29.99
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
Runs server-side · free · no signup
Step by step
- 1Type the partial text to search for in a cell (e.g. D2).
- 2In the result cell, enter =VLOOKUP("*"&D2&"*",$A$2:$B$5,2,0).
- 3The "*"&D2&"*" builds a wildcard pattern that matches any value containing D2 anywhere in the text.
- 4The formula returns the value from column 2 for the first match found.
Tips
- Wildcard lookups are case-insensitive. Use EXACT inside an array formula for a case-sensitive partial match.
- ? matches exactly one character; * matches zero or more. Combine them as needed, e.g. "A?C*" matches 'ABC', 'ABCDE', etc.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does XLOOKUP support wildcards?
Yes — in XLOOKUP, set match_mode to 2 to enable wildcard matching: =XLOOKUP("*"&D2&"*",A2:A5,B2:B5,,2).
What if I want to search only at the start of the text?
Remove the leading wildcard: =VLOOKUP(D2&"*",$A$2:$B$5,2,0) to match values that begin with D2.
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to look up a value and sum the matches
Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.
How-toHow to look up a value across multiple columns
Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.