IFS formula

Occasional Contributor

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.





4 Replies



Does this formula return your expected result?

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:



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?



Maybe with this formula as shown in the attached file.


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,