Forum Discussion
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 the first task to the first person, then the second task to the second person, then the 3rd task to the 3rd person and so on until the first 50 tasks are assigned.
then I need to assign the 51st task to the first person, the 52nd to the second person, and the 53rd task to the 3rd person until that batch of tasks and People are matched.
but this is not linear, I may want to choose task 52 to person 3 because the 3rd task is related to the 52nd task.
i can live without this feature and fine tune the list manually but the main question is how can I look up a name and match it to a task.
i hope this makes sense to you, please ask for any clarification and i shall add that bit.
thanks in advance…
wassim
3 Replies
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:
- Make sure to keep the dollar signs ($) in the Sheet2 range so the list stays locked as you drag the formula down.
- 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.
- 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
Please attach a sample file or share via OneDrive, Google-Drive, Dropbox or similar. Show your input data and then desired output.
Sorry can’t! I am sorry I am not allowed to paste any even with dummy data!
