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
If i would like to add exception keywords, OR Keywords, AND Keywords with above query. Can you suggest with refrence of below example.
| Product name | Brand | Category |
| Huggies Natural Care Refreshing Baby Wipes, Scented, 3 Flip-Top Packs (168 Wipes Total) | Huggies | Wipes |
| Baby Wipes, Huggies Natural Care Refreshing Baby Diaper Wipes, Hypoallergenic, Scented, 10 Flip-Top Packs (560 Wipes Total) | Huggies | Diapers |
| Baby Wipes, Huggies Natural Care Refreshing Baby Diaper Wipes, Hypoallergenic, Scented, 10 Flip-Top Packs (560 Wipes Total) | Huggies | Diapers |
| Baby Wipes, Huggies Natural Care Refreshing Baby Diaper Wipes, Hypoallergenic, Scented, 10 Flip-Top Packs (560 Wipes Total) | Huggies | Wipes |
| Haggie Natural Care Refreshing Baby Wipes, Scented, 3 Flip-Top Packs (168 Wipes Total) | Haggie | Wipes |
Search 'Huggies' from A column,
AND
Search 'wipes' from A column
AND
Search 'Natural', 'Naturel'
NOT
Seach' Doesnot contain 'Diaper', 'Diapers', 'Pant'
Then Out Put should 'Natural'