Forum Discussion

jojomartin845's avatar
jojomartin845
Copper Contributor
May 01, 2026

Put a specific word in a cell based on the word in another cell

Hey everyone, I need help automating the categorisation of my bank statement in excel. I do not have microsoft 365. 

I want excel to look for specific words from my transactions eg Woolworths or McDonalds and then based on that, put another word into the categories cell. For Woolworths I want it to populate groceries, for McDonalds I want it to populate Eating Out. Etc. 

I would really appreciate the help and in the most basic way possible. I only use basic formulas, so array formulas are quite foreign to me. I have tried googling and nothing seems to work quite right. 

Happy to move columns around, but would prefer to keep Date, Amount & Description next to each other as that is the order from my bank statement download. I also don't need the Merchant / Category section in notes, but previous attempts led me to adding them.

Thanks

 

excel
Excel on Mac

6 Replies

  • IlirU's avatar
    IlirU
    Iron Contributor
    May 05, 2026

    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

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 04, 2026

    I only consider using 365, preferably insider beta.

    My strategy is to search for the presence of each merchant within the description, returning the corresponding category where successful.  Because there is the possibility that more than one merchant could be present on a single row, concatenation reduces the categories to a scalar.  This reverse lookup is given by

    Worksheet formula
= REVLOOKUPλ(description, merchant, category)

where

REVLOOKUPλ = LAMBDA(descr, merch, cat,
    MAP(descr, LAMBDA(d, 
        CONCAT(
            MAP(merch, cat, LAMBDA(m, c, 
                XLOOKUP(m, d, c, "", 3))
            )
        )
    ))
);

     

  • MarkBeck54's avatar
    MarkBeck54
    Copper Contributor
    May 03, 2026

    If your version of excel supports xlookup i would use that youtube does good vids on subject

     

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    May 02, 2026

    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.