Forum Discussion

JoeThomas45's avatar
JoeThomas45
Copper Contributor
Feb 05, 2023

Excel Partial Match on Another Column and Categorize

How do I check if one word in a column contains keyword in another column, and then categorize using Excel?

I am trying to categorize my credit card statement.

1. First Excel sheet has transaction list of all the stores, vendors, with extra keywords.
2. Second Excel Sheet has keywords and their categories.

Trying to use Vlookup, partial matches, not sure. There could be 200 transactions in a credit card statement each month, and 50 categories lookup.

 

 

 

 

excel
Formulas and Functions

1 Reply

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Feb 06, 2023

    JoeThomas45 Try following formulas. For Excel-365. See the attach file.

    =@FILTER($H$2:$H$5,COUNTIFS(A2,"*"& $G$2:$G$5 & "*"))

    For previous versions. Using Aggregate(). 

    =IFERROR(@INDEX($H$2:$H$5,AGGREGATE(15,6,ROW($1:$5)/(COUNTIFS(A2,"*"& $G$2:$G$5 & "*")),@ROW($1:$1))),"")

    Using SUMPRODUCT(). 

    =INDEX($H$2:$H$5,SUMPRODUCT(COUNTIFS(A2,"*"& $G$2:$G$5 & "*")*(ROW($H$2:$H$5)-ROW($H$1))))

     

     

Resources