Forum Discussion
HELP!! Stuck on INDEX/MATCH/IF Formula for Days!
Hi there!
I've been trying to create a some-what dynamic Excel worksheet for my monthly expenses, so I can analyze data in real time.
Just a small intro-- I have data imported from my bank statements and formatted the .CSV file.
As you can see, in the image provided:
- The Payee column (Column B) has different transactions that have taken place for a certain time period. And let's also say that I have hundreds of different transactions (omitted here).
- In Cell G4, I have three text criteria i.e. Health, CVS, Prime.
- Cell E3, I have a category column wherein I will insert my formula (read below).
My task at hand is: I want to automate the Category column so that I can create a visual representation of my expenses as I add them month over month. Most transactions are similar every month.
With that in mind, I am trying to figure out how to use an INDEX/MATCH function for Category (Cell E3) where I have multiple criteria but I want Excel to match at least partial words (i.e. Health, CVS, Prime) to my expenses in Payee.
For example: I have multiple CVS and Texas Health transactions and I want both of them to fall under a Category called Medical.
But, I don't know how to make Excel choose from the list of words on the right side, match it with words found in Payee, and return the "Medical" text in Category column.
Please let me know if I am making any sense here and if I can clarify.
13 Replies
I guess to reclassify categories need a table to map each sub category like the first table below。
it looks more easier if deal with sql。
select * from category_and_keywords; select * from classify_to_category_according_to_keywords; select * from classify_to_category_according_to_keywords left join category_and_keywords on instr(payee,keywords)>0select * from category_and_keywords; select * from classify_to_category_according_to_keywords; select * from classify_to_category_according_to_keywords left join category_and_keywords on instr(payee,keywords)>0
- Thank you so much for the consideration!
This is not a direct answer to your question; merely a thought prompted by the fact I was processing csv downloads from my bank last night in order to prepare a tax return.
I found that using Power Query to import (multiple) CSV files into a separate Excel file worked well and allowed quite fine control over the import process. I actually used a copy of last year's Excel file but changed the source to this year's CSV files. 'Refresh all' and I had output tables for income, interest on savings and for investment dividends.
=BYROW(B4:B9,LAMBDA(row,IF(OR(ISNUMBER(SEARCH($G$4:$G$6,row))),"Medical","")))With LAMBDA and helper function BYROW you can try this formula.
=IF(OR(ISNUMBER(SEARCH($G$4:$G$6,B4))),"Medical","")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Sorry for some reason the picture isn't being uploaded.
here is a link for image reference: https://imgur.com/a/gvXe1K7
=IF(OR(ISNUMBER(SEARCH($G$4:$G$6,B4))),"Medical",IF(OR(ISNUMBER(SEARCH($H$4:$H$6,B4))),"Food","Other"))
I think this is because the second OR is missing in the formula. In the above formula i've added the part highlighted in red and it works in my sheet.
I added this picture as a reference to my reply. Sorry, I'm new to the community! Trying to figure out the system (:
- Hi there! Thank you for the response. I really appreciate it so much.
I used the IF nested function as you suggested! It was pretty neat. As you can see, my query was limited to just three options for the "medical" category. Now, my question is, how do I add a section for (lets say) Food? For instance, you can see, Dunkin Donuts, Raising Canes in this example. I am definitely making a mistake in my formula because I want to add multiple criteria (i.e. partial text matches) for different transactions that produce different category. TLDR; how do I add another criteria for other types of transactions to create more categories?!
