Wildcard lookup: contains, not starts with
Wrap the search text in asterisks to find it anywhere in the cell.
Task:
Bank lines carry long, messy descriptions; the payee's short name is somewhere inside each one. In C2:C5 return the cost category for each payee in A2:A5 by finding the description that contains it in E2:F6.
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of Lookup with Wildcards
0/3The same formula in the other shapes it takes at work.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Payee | Category | Bank description | Category | ||
| 2 | VODAFONE | DD VODAFONE LTD REF 448291 | Phone | |||
| 3 | SHELL | CARD 4421 TESCO STORES 2231 | Groceries | |||
| 4 | TESCO | CARD 4421 SHELL GARAGE M4 | Fuel | |||
| 5 | BT | DD BT GROUP PLC 99120 | Broadband | |||
| 6 | SO RENT FLAT 3 | Rent |
What this exercise teachesMay contain the answer
A trailing asterisk alone finds text at the start; one on each side finds it anywhere. Be careful with short search terms: "BT" would also match any description containing "DEBT". The first match wins, so check that the shortest names are not swallowed by longer ones.