Forum Discussion

Santhosh_V's avatar
Santhosh_V
Copper Contributor
Jun 27, 2021
Solved

Need to search multiple words in a cell and get the output based on the word found.

Hi,   I am having a data in column A like below, then I need search for multiple words like "Generic Mailbox", "Distribution", "Non-standard", "NSSR" in the each cell and then if "Generic mailbox"...
developer
excel
Formulas and Functions
Macros and VBA
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Jun 27, 2021

    Santhosh_V 

    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's avatar
HansVogelaar
MVP
Mar 13, 2023

_L_C_ 

This discussion was about returning multiple words if the cell being searched contained more than one of the keywords. I get the impression that you want to return only one word. Is that correct?

_L_C_'s avatar
_L_C_
Copper Contributor
Mar 13, 2023
That's correct. Searching for multiple keywords and returning one word, ultimately allowing me to categorize all my data under 5-7 buckets
  • _L_C_'s avatar
    _L_C_
    Copper Contributor
    Mar 13, 2023
    Thanks so much Hans. You are a lifesaver!

Resources