Find an exact partial match otherwise indicate not found

sphynx25

New Member
Joined
May 23, 2019
Messages
3
Hi everyone!

I’m trying to put together an expense tracker in Excel. The intention is to take an extract from online banking on a monthly basis which would give me a list of all my expenses. The extract includes the date the expense was incurred, a description of the expense, the amount incurred. I have then put a column next to these which indicates the categorisation of the expense, e.g. ‘Utilities’ something like this:

<table>
<tr>
<th>Date incurred</th>
<th>Expense Amount</th>
<th>Expense Description</th>
<th>Category</th>
</tr>
<tr>
<td>2019/05/31</td>
<td>$xx</td>
<td>Direct Debit 0015928 Verizon CR CARD PMNT 934730506</td>
<td>Utilities - Internet</td>
</tr>
<tr>
<td>2019/05/31</td>
<td>$xx</td>
<td>[Supermarket name] J364 [Suburb]</td>
<td>Groceries</td>
</tr>
</table>

So here’s the problem I’m having: ideally I’d like the categorisation to auto populate based on a separate table I’m working on which basically has a unique term which appears in somewhere in the expense description with a second column providing a categorisation. The other table's format presently looks like this:

<table>
<tr>
<th>Search Term</th>
<th>Description</th>
<th>Category</th>
</tr>
<tr>
<td>Verizon</td>
<td>Verizon home internet bill</td>
<td>Utilities - Internet</td>
</tr>
</table>

Given that the merchants decide on the line item content and format, the placement of the vendor name is not often consistent. Also, some stores etc. tend to have the store name in the expense but might have other numbers etc. in the expense description to indicate the specific store location. Ideally I'd like to avoid having to do time-intensive pre-processing of the extract each month, so I want to take check if anything in my reference table has an exact partial match with each line item in the online bank extract. I've tried messing around with variants of LOOKUP and VLOOKUP, but using these results in the formula taking its best guess (which will always be wrong) in cases where the vendor has been seen before and isn't already on the list. Ideally, in these cases, I'd want the formula to inform me that no exact partial match was found which will allow me to manually add it to the table for future lookups.

Any ideas, guys? This one has me pretty stumped short of pulling out VB and working on some macros.

Thanks so much for your help!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Bagloon

New Member
Joined
May 24, 2019
Messages
1
Hi. Try using Find() or Search() functions. These both find text within a cell, irrespective of other characters in the cell. They return the starting position of the searched text, so test the returned value for an error or not.

Regards
 

sphynx25

New Member
Joined
May 23, 2019
Messages
3
Thanks for your response, Bagloon. The issue is that I'm looking for absolutely any word in my reference table in each bank extract line item. Is there a neater way to achieve this than a whole mess of if statements?
 

sphynx25

New Member
Joined
May 23, 2019
Messages
3
Thanks for your help guys! Marcelo, that's exactly what I was looking for. Legend! :D
 

Watch MrExcel Video

Forum statistics

Threads
1,118,207
Messages
5,570,908
Members
412,348
Latest member
NATTS
Top