SOLVED

# Lookup for a partial match on VLOOKUP

Copper 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!

3 Replies
best response confirmed by rosscar (Copper Contributor)
Solution

# Re: Lookup for a partial match on VLOOKUP

``=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.

# Re: Lookup for a partial match on VLOOKUP

Brilliant - Thank you so much!

# Re: Lookup for a partial match on VLOOKUP

You are welcome.
1 best response

Accepted Solutions
best response confirmed by rosscar (Copper Contributor)
Solution

# Re: Lookup for a partial match on VLOOKUP

``=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.