Forum Discussion
Need to search multiple words in a cell and get the output based on the word found.
If you have Excel 2019 or Excel in Microsoft 365, enter the following formula in B1:
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),{"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},""))
This allows for more than one of the search terms in a cell.
Otherwise:
=IFERROR(INDEX({"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},MATCH(TRUE,ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),0)),"")
Fill down from B1.
If you have Excel 2019 or Excel in Microsoft 365, enter the following formula in B1:
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),{"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},""))
This allows for more than one of the search terms in a cell.
Otherwise:
=IFERROR(INDEX({"Shared Mailbox","DL","Corporate Request","Non-Standard Service Request"},MATCH(TRUE,ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR"},A1)),0)),"")
Fill down from B1.
Hi HansVogelaar
Just new around here and saw your formula that was almost great for what I need. Almost because it keeps giving me inaccurate results.
I just know some basic stuff in Excel and I'm not able to go through this.
So I have this data below where I need to insert in C2:C5 a formula that will return as follows:
If "[INTERN]" is found in the cell, output should be INTERN
If "Offensive" is found in the cell, output should be Offensive
If "[INTERN]" and "Offensive" is found in the cell, output should be INTERN Offensive
If none of the above, then the output should be "REGULAR"
I copied the formula and modified as needed, but something is not correct since for the last value it returns the same thing (C4 and C5 are the same). Would you be kind enough to help me solve this formula and have it return as I indicated in D2:D5? I'm struggling for some days now and no result. 🙂
Thanks so much for your help, looking forward to see where I did wrong. 😉
Razvan