Forum Discussion
rosscar
Jan 22, 2023Copper Contributor
Lookup for a partial match on VLOOKUP
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!
=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.
- OliverScheurichGold Contributor
=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.
- rosscarCopper ContributorBrilliant - Thank you so much!
- OliverScheurichGold ContributorYou are welcome.