If you’re trying to distribute tasks fairly among a team, round-robin assignment is one of the simplest methods. Instead of manually assigning each task, you can automate the whole thing in Google Sheets — and yes, it updates itself as you add more tasks or names.
In this tutorial, I’ll show you how to do that with a single dynamic formula that evenly assigns tasks to team members using a round-robin approach.
Setup
You’ll need two columns of input:
- Tasks in column
A2:A(you can have as many as you want) - Names in column
D2:D(the people to assign tasks to)
Let’s say you currently have 15 tasks and 3 names.
Round-Robin Task Assignment Formula in Google Sheets
Paste this into cell B2 (next to your first task):
=ArrayFormula(LET(
tasks, TOCOL(A2:A, 1),
names, TOCOL(D2:D, 1),
tasksSeq, ARRAYFORMULA(MOD(SEQUENCE(ROWS(tasks), 1, 0), COUNTA(names)) + 1),
namesSeq, SEQUENCE(ROWS(names)),
idassign, IFNA(VLOOKUP(A2:A, HSTACK(tasks, tasksSeq), 2, false)),
XLOOKUP(idassign, namesSeq, names, "")
))Done! Each task in column A will now be automatically assigned to a person from your list in D2:D.
What This Formula Does
Here’s a quick breakdown of how the round-robin task assignment formula works — without getting too deep into formula nerd-land:
TOCOL(A2:A, 1): Pulls all tasks into a single, clean vertical list while ignoring any blank cells.TOCOL(D2:D, 1): Does the same for the names — it extracts all non-blank names into a clean list.MOD(..., COUNTA(names)) + 1: Generates a repeating sequence of numbers (like 1, 2, 3, 1, 2, 3…) based on how many names you have. This sequence determines the round-robin assignment pattern.SEQUENCE(ROWS(names)): Creates a list of numbers from 1 to the number of names — this acts as an ID sequence for the XLOOKUP step.HSTACK(tasks, tasksSeq)+VLOOKUP: Combines the tasks with their corresponding round-robin index (the pattern). Then,VLOOKUPfinds which index each task should be assigned to. This step ensures even blank rows won’t break the assignment.XLOOKUP(idassign, namesSeq, names, ""): This is where the actual round-robin task assignment happens. It matches each task’s assigned index to a name in the sequence and returns the correct person.
And the best part?
- It works even if you leave blank cells in your task or name columns.
- You can add more tasks or more names, and the assignments will update automatically.
Round-Robin Task Assignment Example
Let’s say you have this:
Tasks (A2:A):
Task 1
Task 2
Task 3
Task 4
... up to Task 15
Names (D2:D):
Mike
Amal
Roshni
Here’s how the round-robin assignment will look in column B:
And yes — when it reaches Task 4, it loops back to the first name in your list, continuing the round-robin pattern.
Why Use This?
- You don’t have to manually assign or adjust anything.
- It scales with your data — just add more tasks or names.
- It’s cleaner than using scripts or dragging formulas down.
If you’ve got a team and a list of responsibilities, this setup will keep things fair and organized. Perfect for recurring task lists, ticketing queues, or even chore charts.
Final Thoughts
This round-robin task assignment formula is a great example of how Google Sheets can simplify routine processes. Whether you’re working with 5 tasks or 500, you don’t have to touch a thing once this is set up.
Let the formula do the work while you focus on everything else.
