IFS formula

[lhunter700]

I am trying to create a formula to choose values based on a set of rules, given the following situation.

Column A = Value of 1,2 or 3

Column B = 4

Column C = 5

Column D = 6

Column E = 7

I am trying to figure out a formula i can use for column F which will enter the values from Columns A to E in the following priority:

If 4, enter 4.  Otherwise enter 5 AND 6 OR 7 (when available).  Otherwise enter 3,2 or 1.

Right now i have an IFS statement:

IFS(B1=4,4,C1=5,5,D1=6,6,E1=7,7,A1=3,3,A1=2,2,A1=1,1) which partially works but I'm having trouble getting the conditions for 5,6 and 7 met.

Any help would be appreciated.

Thanks,

Lisa

[#2]

@lhunter700 

=IFS(B1=4,4,AND(C1=5,D1=6),56,E1=7,7,A1=3,3,A1=2,2,A1=1,1)

Does this formula return your expected result?

[#3]

Sadly, it does not work for all cases.

The problem is I need it to return 5 and 6 or 5 and 7 (if both have values) and 5,6,7 if all 3 have values. Otherwise, just return 5 or 6 or 7 before moving on to returning 3/2/1.

This formula returns 56 when no data for 4 (even if data for 7) but if data for 5 and 7 but no data for 6 it returns just 7 instead of 5,7.

Also, if i have values for 1 and 6, it is returning 1 instead of 6 (same if a value for 1 and 5).

Does that make sense?

I went ahead and modified your formula to:

=IFS(B2=4,4,AND(C2=5,D2=6),56,AND(C2=5,E2=7),57,AND(C2=5,D2=6,E2=7),567,D2=6,6,C2=5,5,E2=7,7,A2=3,3,A2=2,2,A2=1,1)

This fixes the case with 1 and 6 / 1 and 5 and also when 5 and 7 but not when 5, 6 and 7. So very close ...

If i can't get that last instance to work, it may be ok but do you know if there is a way to return 5,6 instead of 56 with this formula?

[#4]

@lhunter700 

=IFS(B1=4,4,AND(C1=5,D1=6,E1=7),567,AND(C1=5,D1=6),56,AND(C1=5,E1=7),57,C1=5,5,D1=6,6,E1=7,7,A1=3,3,A1=2,2,A1=1,1)

Maybe with this formula as shown in the attached file.

[#5]

That worked! So, i just had to give the case with all 3 options first? Good to know.!

And I figured out how to return 5,6 instead of 56 - i just had to enter it in the formulas as "5,6" instead of 56.

Thanks so much for all of your help,
Lisa