May 04 2022 04:38 AM
Hi!
A newbie on this forum and would never consider my self as an Excel expert
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 value | Formula used |
On-Prem Advantage, 3Y Term Lic | Advantage | =IFERROR(IFS(SEARCH("Advantage";A16);"Advantage");IFS(SEARCH("Essential";A16);"Essential")) |
On-Prem Essential, 5Y Term Lic | Essential | =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
May 04 2022 04:59 AM
SolutionFor example:
=IFS(ISNUMBER(SEARCH("Advantage";A16));"Advantage";ISNUMBER(SEARCH("Essential";A16));"Essential";ISNUMBER(SEARCH("Premier";A16));"Premier";TRUE;"")
May 04 2022 05:06 AM
May 04 2022 05:09 AM
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"};""))