Forum Discussion

Copper Contributor

May 04, 2022

Solved

IFS and SEARCH with three different return values

Hi! A newbie on this forum and would never consider my self as an Excel expert 🙂 Plus I'm not very used to the IFSand SEARCH formulas, so I hope you guys out there might help me. Got a list ...

Formulas and Functions

HansVogelaar
May 04, 2022
Per-Olav_Sandbakk

For example:

=IFS(ISNUMBER(SEARCH("Advantage";A16));"Advantage";ISNUMBER(SEARCH("Essential";A16));"Essential";ISNUMBER(SEARCH("Premier";A16));"Premier";TRUE;"")

HansVogelaar

MVP

May 04, 2022

Per-Olav_Sandbakk

For example:

=IFS(ISNUMBER(SEARCH("Advantage";A16));"Advantage";ISNUMBER(SEARCH("Essential";A16));"Essential";ISNUMBER(SEARCH("Premier";A16));"Premier";TRUE;"")

Holly_Michaelson

Copper Contributor

Nov 16, 2022

HansVogelaar

I have a similar problem, but for the third condition, I want it to return a value for everything else. In other words, the Advantage and Essential arguments work for me, but then for the third variable, I want everything that isn't Advantage or Essential to be labeled as "Other". How would I do that please?

HansVogelaar
MVP
Nov 16, 2022
Holly_Michaelson

Using the same example:

=IFS(ISNUMBER(SEARCH("Advantage"; A16)); "Advantage"; ISNUMBER(SEARCH("Essential"; A16)); "Essential"; TRUE; "Other")

Please note that Per-Olav_Sandbakk uses semi-colon as list separator. If you use comma as list separator, change the formula to

=IFS(ISNUMBER(SEARCH("Advantage", A16)), "Advantage", ISNUMBER(SEARCH("Essential", A16)), "Essential", TRUE, "Other")
- Holly_Michaelson
  Copper Contributor
  Nov 16, 2022
  Yes! Thank you!! And I appreciate you mentioning about the commas. My excel would not accept the semi colons.
  
  With gratitude!
  Holly