Forum Discussion
Put a specific word in a cell based on the word in another cell
You absolutely can do this without Microsoft 365, without array formulas, and without stress.
In cell D2, put this formula:
=IF(ISNUMBER(SEARCH("WOOLWORTHS",C2)),"Groceries",IF(ISNUMBER(SEARCH("MCDONALDS",C2)),"Eating Out",IF(ISNUMBER(SEARCH("PRIME",C2)),"Streaming","")))
Then drag down column D.
How to add more rules
Just replace the final "" with another IF(ISNUMBER(SEARCH("WORD",C2)),"Category", and close with "" at the end.
Example with 5 rules:
=IF(ISNUMBER(SEARCH("WOOLWORTHS",C2)),"Groceries",IF(ISNUMBER(SEARCH("MCDONALDS",C2)),"Eating Out",IF(ISNUMBER(SEARCH("PRIME",C2)),"Streaming",IF(ISNUMBER(SEARCH("AMAZON",C2)),"Shopping",""))))
friendly tip…
If a transaction doesn't match any word, the cell will stay blank. Just manually type the category for those few — or add another rule if you see a pattern.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Hi jojomartin845,
Since you don't have Microsoft 365 then I think you can use Excel for the web. I have given my solution below in Excel for the web (see the screenshot).
In cell A5 I have applied this formula:
=LET(
desc, D5:D10,
merch, M4:M6,
cat, N4:N6,
rwn, SEQUENCE(ROWS(desc)) / SIGN(SEARCH(merch, TRANSPOSE(desc))),
num, TOCOL(TRANSPOSE(rwn), 3),
XLOOKUP(num, SEQUENCE(ROWS(cat)), cat)
)Change the ranges in the formula according to your needs.
HTH
IlirU