Find a price from part of a product name (VLOOKUP wildcards)
The problem
Problem statement
The POS export stores long product names — Arabica Coffee Beans 1kg — but the search box only ever holds a keyword. The keyword is in D2, the price list is A2:B5. Fill the Price cell F2 so typing Coffee still finds the first product whose name contains it.
A partial match needs wildcards on both sides of the keyword and an exact-match lookup — approximate match (TRUE) would silently pick a different row.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | F | |
|---|---|---|---|---|
| 1 | Product | Price | Keyword | Price |
| 2 | Arabica Coffee Beans 1kg | 18 | Coffee | |
| 3 | Decaf Coffee 500g | 15 | ||
| 4 | Green Tea 250g | 12 | ||
| 5 | Cold Brew Coffee 1L | 21 |
Notes
The pattern is built, not typed: "*" & D2 & "*" turns Coffee into *Coffee*, which matches the text anywhere in the name. Wildcards only work with the exact-match argument (FALSE); with TRUE, the asterisks are ignored and the result is a surprise. When several rows match, the first one from the top wins.
How to approach
Your answer goes in F2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is F2
- The keyword is in D2; names in A2:A5, prices in B2:B5
- Wrap the keyword in asterisks so it can match anywhere inside the name
- Shape: =VLOOKUP(?, ?, ?, FALSE) — the pattern is the first argument
Official solutions
Show 2 solutions — try it yourself first
Approach 1: VLOOKUP with a wildcard pattern
=VLOOKUP("*"&$D$2&"*",$A$2:$B$5,2,FALSE)
The asterisks stand for "anything before and after", so *Coffee* matches Arabica Coffee Beans, Decaf Coffee and Cold Brew Coffee. VLOOKUP returns the first hit from the top and reads column 2.
Approach 2: INDEX + MATCH with the same pattern
=INDEX($B$2:$B$5,MATCH("*"&$D$2&"*",$A$2:$A$5,0))
MATCH also understands wildcards, and it returns a position rather than binding you to a column number. Useful when the value you want sits to the left of the name.
Function guide
FAQ
Can VLOOKUP match part of a cell instead of the whole value?
Yes — with wildcards. Build "*"&keyword&"*" and keep the last argument FALSE (exact match). The asterisk stands for any run of characters, so a keyword matches anywhere in the name.
Why build the pattern with & instead of typing "*Coffee*"?
Hardcoding the keyword means the formula only ever answers one question. "*"&D2&"*" reads whatever is in D2, so the same cell can search Coffee today and Tea tomorrow (case 2).
Which row does VLOOKUP return when several match?
The first one from the top. In case 1 three products contain Coffee and the answer is the Arabica row. If you need all matches, VLOOKUP is the wrong tool — that calls for FILTER.