SOLVED

IFS and SEARCH with three different return values

%3CLINGO-SUB%20id%3D%22lingo-sub-3302235%22%20slang%3D%22en-US%22%3EIFS%20and%20SEARCH%20with%20three%20different%20return%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302235%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20newbie%20on%20this%20forum%20and%20would%20never%20consider%20my%20self%20as%20an%20Excel%20expert%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Plus%20I'm%20not%20very%20used%20to%20the%20IFSand%20SEARCH%20formulas%2C%20so%20I%20hope%20you%20guys%20out%20there%20might%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGot%20a%20list%20containing%20different%20Subscription%20descriptions.%26nbsp%3B%20I'm%20trying%20to%20use%20the%20IFS%20combined%20with%20SEARCH%20in%20order%20to%20extract%20the%20subscription%20level%20as%20the%20return%20value.%26nbsp%3B%20There's%20three%20different%20levels%3B%20Essential%2C%20Advantage%20and%20Premium%20and%20I%20want%20them%20to%20be%20the%20return%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20able%20to%20get%20it%20work%20with%20two%20IFS%2FSEARCH%20in%20the%20formula%2C%20but%20when%20adding%20the%20third%20I'm%20told%20there%20are%20to%20many%20arguments%20in%20the%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221192%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22250%22%3ESubscription%20Description%20(Search)%3C%2FTD%3E%3CTD%20width%3D%2291%22%3EReturn%20value%3C%2FTD%3E%3CTD%20width%3D%22851%22%3EFormula%20used%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOn-Prem%20Advantage%2C%203Y%20Term%20Lic%3C%2FTD%3E%3CTD%3EAdvantage%3C%2FTD%3E%3CTD%3E%3DIFERROR(IFS(SEARCH(%22Advantage%22%3BA16)%3B%22Advantage%22)%3BIFS(SEARCH(%22Essential%22%3BA16)%3B%22Essential%22))%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOn-Prem%20Essential%2C%205Y%20Term%20Lic%3C%2FTD%3E%3CTD%3EEssential%3C%2FTD%3E%3CTD%3E%3DIFERROR(IFS(SEARCH(%22Advantage%22%3BA17)%3B%22Advantage%22)%3BIFS(SEARCH(%22Essential%22%3BA17)%3B%22Essential%22))%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EOn-Prem%20Premier%2C%203%20Year%20Term%20License%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3DIFERROR(IFS(SEARCH(%22Advantage%22%3BA18)%3B%22Advantage%22)%3BIFS(SEARCH(%22Essential%22%3BA18)%3B%22Essential%22)%3BIFS(SEARCH(%22Premier%22%3BA18)%3B%22Premier%22))%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20how%20do%20i%20navigate%20around%20this%20issue%3F%20Anybody%20out%20there%20that%20might%20help%20me%20with%20this%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ergds%2C%3C%2FP%3E%3CP%3EPer-Olav%20Sandbakk%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3302235%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302260%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20and%20SEARCH%20with%20three%20different%20return%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380629%22%20target%3D%22_blank%22%3E%40Per-Olav_Sandbakk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlternatively%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(LOOKUP(1000%3BSEARCH(%7B%22Advantage%22%3B%22Essential%22%3B%22Premier%22%7D%3BA16)%3B%7B%22Advantage%22%3B%22Essential%22%3B%22Premier%22%7D)%3B%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXTJOIN(%22%22%3BTRUE%3BIF(ISNUMBER(SEARCH(%7B%22Advantage%22%3B%22Essential%22%3B%22Premier%22%7D%3BA16))%3B%7B%22Advantage%22%3B%22Essential%22%3B%22Premier%22%7D%3B%22%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302257%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20and%20SEARCH%20with%20three%20different%20return%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302257%22%20slang%3D%22en-US%22%3EWell%20that%20worked%20brilliantly!%20Thanks%20a%20bunch%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F21903%22%20target%3D%22_blank%22%3E%40hans%3C%2FA%3E!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302253%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20and%20SEARCH%20with%20three%20different%20return%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380629%22%20target%3D%22_blank%22%3E%40Per-Olav_Sandbakk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFS(ISNUMBER(SEARCH(%22Advantage%22%3BA16))%3B%22Advantage%22%3BISNUMBER(SEARCH(%22Essential%22%3BA16))%3B%22Essential%22%3BISNUMBER(SEARCH(%22Premier%22%3BA16))%3B%22Premier%22%3BTRUE%3B%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

 

3 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"};""))