SOLVED

IFS and SEARCH with three different return values

New Contributor

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 containing different Subscription descriptions.  I'm trying to use the IFS combined with SEARCH in order to extract the subscription level as the return value.  There's three different levels; Essential, Advantage and Premium and I want them to be the return value.

 

I'm able to get it work with two IFS/SEARCH in the formula, but when adding the third I'm told there are to many arguments in the function.

 

Subscription Description (Search)Return valueFormula used
On-Prem Advantage, 3Y Term LicAdvantage=IFERROR(IFS(SEARCH("Advantage";A16);"Advantage");IFS(SEARCH("Essential";A16);"Essential"))
On-Prem Essential, 5Y Term LicEssential=IFERROR(IFS(SEARCH("Advantage";A17);"Advantage");IFS(SEARCH("Essential";A17);"Essential"))
On-Prem Premier, 3 Year Term License =IFERROR(IFS(SEARCH("Advantage";A18);"Advantage");IFS(SEARCH("Essential";A18);"Essential");IFS(SEARCH("Premier";A18);"Premier"))

 

So how do i navigate around this issue? Anybody out there that might help me with this formula?

 

rgds,

Per-Olav Sandbakk

 

6 Replies
best response confirmed by mathetes (Respected Contributor)
Solution

@Per-Olav_Sandbakk 

For example:

 

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

Well that worked brilliantly! Thanks a bunch @hans!

@Per-Olav_Sandbakk 

Alternatively:

 

=IFERROR(LOOKUP(1000;SEARCH({"Advantage";"Essential";"Premier"};A16);{"Advantage";"Essential";"Premier"});"")

 

or

 

=TEXTJOIN("";TRUE;IF(ISNUMBER(SEARCH({"Advantage";"Essential";"Premier"};A16));{"Advantage";"Essential";"Premier"};""))

 

@Hans Vogelaar 

 

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?

@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")

Yes! Thank you!! And I appreciate you mentioning about the commas. My excel would not accept the semi colons.

With gratitude!
Holly