Forum Discussion
JoeThomas45
Feb 05, 2023Copper Contributor
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 transact...
Harun24HR
Feb 06, 2023Bronze Contributor
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))))