How to auto assign next letter from a list into a cell depending on value of another?

Here is a basic sheet with list of days of the month that change according to date in a specified cell, trying to find a way to automatically assign next letter from a list and place it in cell according to what day it is in neighbor cell.

This is the closest I’ve gotten using IFS:

=IFS($B$4:$B$37="MONDAY", SORTN($F$4:$F$7,1,,RANDARRAY(4),),$B$4:$B$37="TUESDAY", SORTN($G$4:$G$7,1,,RANDARRAY(4),), $B$4:$B$37="WEDNESDAY", SORTN($H$4:$H$7,1,,RANDARRAY(4),),$B$4:$B$37="THURSDAY", SORTN($I$4:$I$7,1,,RANDARRAY(4),), $B$4:$B$37="FRIDAY", SORTN($J$4:$J$7,1,,RANDARRAY(4),), $B$4:$B$37="SATURDAY", SORTN($K$4:$K$7,1,,RANDARRAY(4),), $B$4:$B$37="SUNDAY", SORTN($L$4:$L$7,1,,RANDARRAY(4),))

This method doesn’t choose next in line but randomly chooses from within the range. This could potentially work but one issue is that this can potentially assign same letter three Mondays in a row, another issue is the default “onChange” setting causing data from formulas to change with every edit.

Description:F3:L7 Alphabet represents (Peoples names or anything else) that are available (to work on those days) & can appear for each day on Calendar found in columns B:C. Column D in image is currently the expected output. Would need it to change automatically as the calendar changes, “Monday” will not always appear in B4 when date in A4 is changed.

FIXED Sheet link (Most wording is in Spanish):



Fixed! Used an Arrayformula to assign a number to each duplicate, got the idea here then using IFS formula from description. Link to working sheet will be in description.

Leave a Reply

Your email address will not be published. Required fields are marked *