Forum Discussion

WassimN's avatar
WassimN
Copper Contributor
Mar 31, 2026

Need a consecutive XLookup

Hello All,   OK let me describe what I am trying to do: I need to create a Task List. In this list I will have let’s say 200 tasks, and 50 people to perform these tasks. i need a way to assign th...
developer
excel
Formulas and Functions
Macros and VBA
Olufemi7's avatar
Olufemi7
Iron Contributor
Apr 01, 2026

HelloWassimN​, 

I have run into this exact scenario before. While XLOOKUP is usually the go-to for matching data, it is actually not the best fit here because you are trying to create a repeating cycle rather than looking up a specific value.

The easiest way to automate this is with a "Round Robin" formula using INDEX and MOD.

If your 50 people are listed in Sheet2!A2:A51, put this in cell B2 and drag it down:

=INDEX(Sheet2!A2:A51, MOD(ROW(A1)-1, 50) + 1)

A couple of quick tips to make sure it works the first time:

  1. Make sure to keep the dollar signs ($) in the Sheet2 range so the list stays locked as you drag the formula down.
  2. Even if your data starts on a different row, keep A1 in the ROW part. That is just a counter to make the math start at 1.
  3. If you are in a region that uses semicolons instead of commas as separators, just swap them out in the formula.

For your "non-linear" requirement where you want to override a specific task, I would suggest adding a Manual Override column in Column C. Then, in Column D, use this:

=IF(C2<>"", C2, B2)

This way, the spreadsheet stays automated by default, but if you type a specific name into Column C, it will prioritize that person for that task and ignore the formula.

If you are on Excel 365, you can actually do the whole thing in one go without dragging:

=INDEX(Sheet2!A2:A51, MOD(SEQUENCE(200, 1, 0), 50) + 1)

If you want to check the official documentation on how these functions handle the looping math, here are the links:

INDEX: https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd

MOD: https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3