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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.