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.
HansVogelaar
Thanks for the great formula, it works perfectly for me.
I was wondering if there is a way to use a list of the texts, instead of typing it like the SEARCH({"xxx","yyyy"...})?
Thanks in advance!
It depends. The above example is complicated because we have to check for the occurrence of two words together. If we only have to search for single words, we could use a lookup list.
Thanks HansVogelaar . I created a named range "list_FileExtension" and replaced the strings separated by comma in your formula, with the named range as below. It worked. Thank you for your help.
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(list_FileExtension,B16)),list_FileExtension,""))
The output came as required:5, 6, 7, 8, XNAP, pdf, Title
5, 6, 7, 8, XNAP, pdf, Title were part of the values, in the named range, to be searched in the column B.