IFS with wildcards

[itchyfinger]

I am attempting to extract a word, preferably wildcard style from a cell and then place the desired text in another cell. This is the formula that works for a specific word  =IFS(A12="smoke","Smoke Detector",A12="heat","Heat Detector",A12="pull","Pull Station")  unfortunately if there are any other words in A12 I do not get the desired result.  I attempted using the wildcard "*smoke*" and this did not work.  The challenge will be if the imported data to the cell looks like SMOKEDETECTOR or HALLSMOKE which are completely likely.  I was able to do a (ifsnumber(search( and find it that way, but it did not get the multiplicity of results I was looking for.

[#2]

Hi @itchyfinger 

If function in Excel will not work when using wildcards directly after an equals sign in a formula. There is other way to work around with this method.

This formula will solve the issue

=IFS(
ISNUMBER(SEARCH("smoke",A12)),"Smoke Detector",
ISNUMBER(SEARCH("heat",A12)),"Heat Detector",
ISNUMBER(SEARCH("pull",A12)),"Pull Station"
)

Regards, Faraz Shaikh, MCT, MIE, MOS Master, Excel Expert 

www.ExcelExciting.com

[#3]

@Faraz Shaikh 

Amazing, worked well.  Thanks so much.

[#4]

@itchyfinger 

As variant

=IFERROR(
  CHOOSE(COUNTIFS(A12,"*smoke*")+2*COUNTIFS(A12,"*heat*")+3*COUNTIFS(A12,"*pull*"),
  "Smoke Detector","Heat Detector","Pull Station"),
"no such")