Lookup for a partial match on VLOOKUP

[rosscar]

I'm looking to automate my budgeting and roughly figure out how much I am spending on different categories each month. My household uses multiple bank accounts/credit cards for various bills. I plan to export the csv bank statements for each account and place them into an Excel Book for sorting. To sort, I am looking to categorise recurring expenses (such as transport and bills) to see what our monthly combined bills have come to and then compare this value to previous months.

I have set up a table of these recurring payments (I5:J8). I am looking for a function that will read the item description in Column C, match it with the terms in Column I and provide the return value (in Column J) within Column E.

For example: For C5, the function will see the term in I5 and will return Transport (from J5) within cell E5. (Below is an example of how the completed table should look).

I have tried combinations of VLookup but this doesn't work when the values in Column C only partially match those in Column I. I have also used If & Search functions but I can't get this to work either.

Any help would be greatly appreciated. Thanks!

[#2 · Accepted solution]

@rosscar 

=INDEX($J$5:$J$9,MATCH(TRUE,ISNUMBER(SEARCH("*"&$I$5:$I$9&"*",C5)),0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

Brilliant - Thank you so much!

[#4]

You are welcome.