Round-Robin Task Assignment in Google Sheets (No Scripts)

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.

Table of Contents

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, VLOOKUP finds 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:

Round-robin task assignment formula example in Google Sheets using dynamic LET and ArrayFormula

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.