lookup for partial text, then repeat it many times

[tothgabor]

Hi everyone,

I am searching for a non-VBA solution for the following problem if it exists.

I download regularly my bank account transactions from my netbank, which looks like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]booking text[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]Bezahlung Karte MC/000000230 28 00000014 MAESTRO POS 02.11.15 13:35K001 BURGER KING\\BERLIN\12 527 8003400EUR[/TD]
[TD]14,98[/TD]
[/TR]
[TR]
[TD]ERGO-POLIZZE KF-002314880 FUER ASTRA-H-CC 1.6 5T W-62959D - KFZ INDIVIDUELL RECHNUNG NR. 001127330655 OG/000000229 VBOEATWWXXX AT324300041455080014 ERGO Versicherung AG[/TD]
[TD]103,93[/TD]
[/TR]
[TR]
[TD]POS 4,99 AT K1 17.10. 14:27 VD/000000210 IKEA WIEN NORD WIEN 1220[/TD]
[TD]4,99[/TD]
[/TR]
</tbody>[/TABLE]

I would like to look up a spending category for each line of transaction based on a mapping table:[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]key word[/TD]
[TD]category[/TD]
[/TR]
[TR]
[TD]BURGER KING[/TD]
[TD]food[/TD]
[/TR]
[TR]
[TD]ERGO Versicherung[/TD]
[TD]car[/TD]
[/TR]
[TR]
[TD]IKEA[/TD]
[TD]home[/TD]
[/TR]
</tbody>[/TABLE]

These are only examples, the tables are quite long, and every month there are some new items which should be inserted into the mapping. Number of categories are given. Due to the large number of mapping items this solution is not working: iferror(vlookup("*BURGER KING*";mapping;2;0);vlookup("*ERGO*";mapping;2;0)).... The booking text field in the bank transactions doesn't show a reliable and consistent pattern, it is not possible to lookup within the cell with text formulas like MID, LEFT, RIGHT, SEARCH or their combination. Or I just haven't discovered it yet.

How could I lookup for each line the related category?

Thanks for the answers!

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$A2&" "),Categories)

where A2 houses a booking text.