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.
@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))))