I have a recurring task to classify Bank transactions into different categories based on the text data present in the Bank Memo. So the idea is to check if the Bank Memo contains any of the keywords from a pre-defined table with 2 columns, one containing the Keywords and the second containing the corresponding Category for that keyword. If a cell with the Bank Memo contains the keywords from the pre-defined table, it should perform a Vlookup and return the correct Category of the keyword from the table.
To achieve this goal, I entered the below formula
=IFERROR(VLOOKUP(INDEX(Criteria!A:A, MATCH(1, COUNTIF(C2, Criteria!A:A), 0)),Criteria!A:B,2,0),"")
where Criteria!A:A is the column containing the keywords and Criteria!A:B is the pre-defined table with Keywords and Category columns.
The result was somewhat right but I observed an issue for certain keywords which were similar but had different Categories. e.g. If Bank Memo contains the text "Amazon", the correct Category should be "Office Expense" but if Bank Memo contains the text "Amazon Music" or "Amazon Prime Video", it should be classified as "Distributions"
However with above formula Amazon, Amazon Music and Amazon Prime Video were all classified as Office Expense
Could you please let me know if there is any better way to write this formula?