Lookup & Reference
Repeat a 1..N number sequence & round-robin assign leads from a list (MOD & INDEX)
Cycle through non-empty names in a fixed roster range. Learn how roster changes affect calculated assignments.
Round-robin assignment from a bounded roster
Use a zero-based, nonnegative whole-number ticket index in column A. FILTER removes blank names from E2:E5 before INDEX chooses a slot; this avoids the gap problem caused by counting names but indexing the unfiltered range.
The roster range has four slots. Adding a name inside E2:E5 changes the cycle; a name outside that range is not included until you extend all roster references. Duplicate names receive multiple turns. Whitespace-only names should be cleaned first.
This distributes ticket counts, not workload or urgency. Changing the roster recalculates existing assignments too. Preserve historical assignments as values or in a dedicated routing system when they must not change.
| Ticket Zero-Idx (A) | Cycle Number (1..3) | Assigned Rep =INDEX(Roster, Cycle) | Roster Size Change |
|---|---|---|---|
| 0 (Ticket #1) | 1 | Ana | 1st rep |
| 1 (Ticket #2) | 2 | Ben | 2nd rep |
| 2 (Ticket #3) | 3 | Cam | 3rd rep |
| 3 (Ticket #4) | 1 (Wraps!) | Ana (or Dan if 4th rep added!) | Automatic wraparound |
Skip empty roster slots before indexing
Evaluated on Ticket #4 (zero-based index A5 = 3): wraps back to Slot 1 ("Ana") across a 3-person roster.
=IF(COUNTA($E$2:$E$5)=0,"No active reps",IF(OR(NOT(ISNUMBER(A5)),A5<0,A5<>INT(A5)),"Invalid index",LET(roster,FILTER($E$2:$E$5,$E$2:$E$5<>""),INDEX(roster,MOD(A5,ROWS(roster))+1))))Generate repeating 1..K numeric cycle (1, 2, 3, 1, 2, 3...)
The numeric cycle assumes at least one name and a nonnegative integer index. The assignment formula above adds validation and skips empty roster positions.
=MOD(A5, COUNTA($E$2:$E$5)) + 1Try the example
Use the original values or change them to test your understanding.