Unmatched merchants on a euro account are usually not unknown shops. They are the same shops spelled several ways, with accents dropped, words abbreviated and a terminal code on the end. Fix the spelling variants first with a mapping table, and the category work shrinks to a few dozen decisions.
If you hold accounts in more than one country, or travel often, the problem multiplies: one supermarket can appear under its local name, a shortened name and a card-network name in the same month.
Why do the same shops show up as different names?
- Accents and special characters are dropped or replaced differently between systems.
- Descriptions are cut at a fixed length, so long names end mid-word.
- Card payments carry a terminal or branch code, while a direct debit carries a reference.
- Online purchases show the payment processor before the shop.
Knowing which cause you are looking at tells you whether to match on the start of the name, the middle, or to give up on the row and fix it by hand.
How do you build the mapping table?
Create a second sheet called Mapping with two columns: raw text to look for in column A and clean merchant name in column B. In the main sheet, with raw descriptions in column C, look up the clean name with this formula, which works in Excel 365, Excel 2021 and Google Sheets:
=XLOOKUP(LEFT(C2, 10), Mapping!$A$2:$A$120, Mapping!$B$2:$B$120, "Unmapped")
Match on a short stem, ten characters here, because the ends of descriptions are the unreliable part. Add a column beside it that gives each clean name a category in the same way, with a second lookup into a table of clean name and category.
A worked example in euros
Say a month shows 92 unmatched rows worth €1,745. After you add five stems to the mapping table, the picture changes as follows.
| Stem added | Variants covered | Rows | Euros |
|---|---|---|---|
| MARKTHAL | 5 | 21 | €389 |
| CAFE DEL | 4 | 16 | €142 |
| TRANSIT | 3 | 14 | €198 |
| ONLINE SH | 6 | 11 | €265 |
| Four smaller stems | 9 | 12 | €171 |
The table accounts for 74 rows and €1,165 (389 + 142 + 198 + 265 + 171). The other 18 rows, worth €580, are single visits you can handle later. One hour at most, and about two thirds of the euros have a home.
What about payments that are not shops?
Standing orders, transfers to family, rent and shared-flat payments often appear as names of people. Give each recurring person a clean label and a category such as Rent or Shared costs, and treat transfers between your own accounts as transfers rather than spending, so they do not inflate your totals.
Which should you do first, fix names or fix categories?
- Fix names: map every variant to one clean merchant.
- Fix categories: give each clean merchant one default category.
- Fix exceptions: correct individual rows where the default is wrong.
- Review what is left unmapped and decide whether it deserves a stem.
Doing it in this order means each decision is made once. Doing it row by row means you make the same decision nine times for one bakery.
A short list to keep next to the sheet
- Keep stems short; the front of a description is the stable part.
- Never delete raw descriptions; add columns instead.
- Look at the largest unmapped euro amounts first.
- Re-run your lookup each month and add only new stems.
Rules about reclaiming or reporting any sales tax differ by country and by person, so keep this exercise about records and talk to a local accountant for anything beyond that.
Useful ideas start with a clearer picture of your own spending.
Explore your reports
