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.
In H3:
=IFERROR(INDEX($J$2:$P$2,MATCH(TRUE,ISNUMBER(SEARCH($J$1:$P$1,C3)),0)),"")
or
=IFERROR(LOOKUP(2,1/SEARCH($J$1:$P$1,C3),$J$2:$P$2),"")
Format H3 as a percentage with 2 decimal places, then fill down.
dear Hans
Hi
Thank you so much for your rapid response and quite helpful formulas.