=IFS(SEARCH("C*",A9),"Cake",SEARCH("K*",A9),"Cookies",SEARCH("B*",A9),"Bread & Bun",SEARCH("Y*",A9),"Pastry")

It works for the first criteria and returns "cake" but won't work for the others. I keep getting a #VALUE error. Can help please??

CodePudding user response：

SEARCH isn't a Boolean-valued function. It doesn't return FALSE if the string isn't found -- it returns a #VALUE! error which isn't coerced to FALSE.

What you could do is wrap everything like SEARCH("C*",A9) with ISNUMBER(): ISNUMBER(SEARCH("C*",A9)) since Excel can tell that #VALUE! isn't a number.