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.
I am trying to use this formula to pull information from a sheet called "host report" if the cell contains the words "ETA" and "Arrival". Any suggestions?
=INDEX('Host Report'!A:W,MATCH(C7&{"ETA","Arrival"},'Host Report'!V:V&'Host Report'!X:X,0),19)