I am looking to be able to process a large set of data and and find a set of predefined words in a string and return an alternate if found.. I have found i can do this individually with multiple FIND or SEARCH functions inside an IFS statement.. i want to include an OR in there though so i don’t have to report as much… EG
Data:
Hello World
Hi World
Goodbye World
Bye World
I basically want to just return "Welcome" if either Hello or Hi are found in a cell
I know i can do it with htis: =IFS(ISNUMBER(SEARCH("Hello",[@Column1])),"Welcome",(ISNUMBER(SEARCH("Hi",[@Column1]))),"Welcome",TRUE,"")
But would like a shorter way, since in reality my data set is much larger. I tried this: =IF(ISNUMBER(FIND(OR([@Column1]="Hello",[@Column1]="HI"),"Welcome","")),"")
but just get a false response
also, want to keep it out of macros
>Solution :
You can do:
=IF( OR( ISNUMBER( SEARCH( {"Hello","Hi"}, [@Column1]) ) ), "Welcome", "" )