Any way to replace "IFS" in Excel 2016?

[Excel43]

I have a deadline tomorrow and I need to change the below code to something that works for Excel 2016. Right now I am using the IFS formula, but it is only working in Excel 2019. Is it any way to rewrite below formula below for the 2016 version or am I screwed?

=INDEX(Vikta;SMALL(IF(Vikta[Grupp]=I3;IFS(Vikta[Grupper]=1;ROW(Vikta)-4;Vikta[Grupper]=2;ROW(Vikta)-4;Vikta[Grupper]=3;ROW(Vikta)-4;Vikta[Grupper]=4;ROW(Vikta)-4;Vikta[Grupper]=5;ROW(Vikta)-4;Vikta[Grupper]=6;ROW(Vikta)-4;Vikta[Grupper]=7;ROW(Vikta)-4;Vikta[Grupper]=8;ROW(Vikta)-4));ROW($1:$1));27)

 

[VBE313]

yes, you need to write a bunch of nested IFs instead of =IFS.

 

[MARK858]

Try a UDF (below is untested with your formula)...

Public Function IFSnew(ParamArray myArgs() As Variant) As Variant
    Dim i As Long
    i = 0
    Do Until CBool(myArgs(i)) Or (i >= UBound(myArgs))
        i = i + 2
    Loop
    If i < UBound(myArgs) Then
        IFSnew = myArgs(i + 1)
    End If
End Function

Book1
	A	B	C
1	3		Yes
Sheet3

Cell Formulas
Range	Formula
C1	C1	=IFSnew(A1=3,"Yes",A1=5,"No",A1=7,"Maybe")

 

[Excel43]

So I tried this, but it gives me an error, it probably has to do with the parantheses? But I cant seem to find where the error is.. any idea?

=INDEX(Vikta;SMALL(IF(Vikta[Grupp]=I4;IF(Vikta[Grupper]=1;ROW(Vikta)-4;IF(Vikta[Grupper]=2;ROW(Vikta)-4;IF(Vikta[Grupper]=3;ROW(Vikta)-4;IF(Vikta[Grupper]=4;ROW(Vikta)-4;IF(Vikta[Grupper]=5;ROW(Vikta)-4;IF(Vikta[Grupper]=6;ROW(Vikta)-4;IF(Vikta[Grupper]=7;ROW(Vikta)-4;IF(Vikta[Grupper]=8;ROW(Vikta)-4))))))));ROW($1:$1));27)

 

[Excel43]

Thanks Mark858, but it would be best if it could be solved without vba cause it might be used on the web

 

[Eric W]

I had a hard time trying to deduce what your table looks like, and didn't really succeed. But your formula compares the same value against 8 integers, and returns the same result. In theory, this should do the same thing, but I haven't been able to test it:

=INDEX(Vikta;SMALL(IF(Vikta[Grupp]=I3;IF(OR(Vikta[Grupper]={1;2;3;4;5;6;7;8}),ROW(Vikta)-4));1);27)

The formula overall doesn't make a lot of sense to me. Perhaps if you explained what it is supposed to, that would help.