HELP!! Stuck on INDEX/MATCH/IF Formula for Days!

Occasional Contributor

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. 

 

Excel.PNG

 

As you can see, in the image provided:

  1. 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).
  2. In Cell G4, I have three text criteria i.e. Health, CVS, Prime.
  3. 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

@BurhanML 

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

payee.JPG 

@BurhanML 

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

category.JPG 

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

I added this picture as a reference to my reply. Sorry, I'm new to the community! Trying to figure out the system (:

 

@Quadruple_Pawn 

@Quadruple_Pawn 

 

Sorry for some reason the picture isn't being uploaded. 

 

here is a link for image reference: Excel Formula Error 

@BurhanML 

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

medical.JPG

Can you explain to me why we have an OR formula in this? I feel like I'm failing to understand where we apply the logical statement here. Also, tell me if I understand this correctly: SEARCH is able to partially match KFC, dunkin, raising even though the Payee description doesn't exactly match the keywords?

@BurhanML 

In order to see what the OR(ISNUMBER(SEARCH( formula does you can select the ISNUMBER(SEARCH( formula as shown in the screenshot.

select isnumber search.JPG

 

 

Then click the F9 button to view the evaluations of the formula. In the example the results are FALSE, FALSE, TRUE (FALSCH; FALSCH; WAHR in german Excel). The result means that "raising" and "dunkin" don't appear in cell B10 "KFC payment" but "KFC" does appear. The result is FALSE, FALSE, TRUE (in this order) because "raising", "dunkin" and "KFC" are in cells H4:H6.

 

The OR function checks if any of the results from the ISNUMBER(SEARCH( function is TRUE.

 

In order to escape this view you can either press ctrl+Z or click the highlighted red sign on the left.

F9.JPG

 

In order to see what the SEARCH formula returns you can select SEARCH(H4:H6;B10) in the formula bar and click F9. You can do this in the same way as with the ISNUMBER(SEARCH( formula.

 

@Quadruple_Pawn 

 

Dear Quadruple_Pawn,

I have one final question. Please see attached picture as reference: Excel Reference Image 

 

As you can see, I have moments where my Amazon (and other categories also) have some transactions that have refunded back to my card. I tried adding another (IF(OR(ISNUMBER(SEARCH( formula where I made the logical statement > 0 and if it was true, it would return a "Refunds" text string.

 

But my formula is not working. How do I incorporate the fact that some transactions are expenses and some are refunds from the same vendor, but I want it to be in two different categories so that it doesn't sum up and give me a different net result!

@BurhanML 

=IF(AND(OR(ISNUMBER(SEARCH($G$3:$G$4,C3))),D3<0),"Refund",IF(AND(OR(ISNUMBER(SEARCH($G$3:$G$4,C3))),D3>0),"Payment",""))

 

You can try this formula. The AND formula checks two criteria in this case. Firstly the ISNUMBER(SEARCH( returns the TRUE or FALSE for the search strings of range G3:G4 and OR returns a single (boolean) result which is either TRUE or FALSE. Secondly D3<0 simply checks if the amount is less than 0 and returns another single (boolean) result, either TRUE or FALSE. Finally the AND function checks if the two criteria are both TRUE and returns either TRUE or FALSE.

 

refund.JPG

@BurhanML 

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.

 

 

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)>0

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)>0

 

 

Screenshot_2022-12-11-21-27-23-789_cn.uujian.browser.jpg

Thank you so much for the consideration!