Lookup Rotating List from week number

[matt0020190]

Hi all

Hope you can help?

This one is probably simple but is bamboozling me..... 

I have four names:

Jane

Bob

John

Martin

I need a non VBA method to display based on the week number who's turn it is to wash up...

For example, I would set who starts first. Ie. WK46 - Jane

Then, I would like a formula to based on the week number select whos turn it is...

E.g. in cell A1 = 46

           cell A2 formula would equal =Jane

E.g. in cell A1 = 48

          cell A2 would say = John

Can this be achieved without planning out the 52 weeks of the year in a vlookup table? I am ideally looking for some formula that works out depending on the week number and pattern whos turn would be next....

Any help appreciated!

Many thanks

Matt

[#2]

something like =CHOOSE(MOD(A1:A52,4)+1,"Jane","Bob","John","Martin")
You can tweak it by adding some offset inside the MOD() and/or changing the order of people and you can also use INDEX() instead of CHOOSE() to select from an array/range. The point is by using the MOD() you get a number from 0 to 3 (and why I +1). You can also replace the 4 inside the MOD() with a COUNTA() to make is more general for a list/range.

[#3]

@matt0020190 Or perhaps enter in A1:

=WEEKNUM(TODAY())

 to get the current week number. And in A2, @mtarler 's formula, slightly modified:

=CHOOSE(MOD(A1,4)+1,"John","Martin","Jane","Bob")

[#4]

Thanks for the reply. Makes sense. A little confused on how to apply to the following though.

Suppose this is my list....

Index ------Name-------WK
0 ---------- Bob----------44
1-----------Jane----------45
2-----------John---------46
3----------Martin--------47

How would I put this in a formula to display as follows (based on the taking in turns theory)

A1 B1
Week 45 is: Jane

A1 B1
Week 48 is: Bob

In simple terms, I know what you mean but cant translate it into my exact purpose. I want to specify the start week number for each name in the above list. Then I want a dynamic cell that will change the name based on the current week number following the pattern of taking in turns all throughout the year.

Can you help me please get over the line?

Many thanks again!

[#5]

Thanks, as per my reply to @mtarler can you support?

[#6]

You would need to just change the order in which the names are listed in your formula. The formula mtarler and Riny provided go through the listed names on a rolling basis, none are repeated twice in a row and each week steps through the list of names from "John" > "Martin" > "Jane" >"Bob" and back again. If you want to change the specific way these appear or force a name to start on a certain week, just change the order in which they're listed.

[#7]

@matt0020190  i don't know that I fully understand what you want to have fixed/set and such.  here is my best guess.  you have a list of names (col E in my example) and that list of names must be rotated throughout the year such that Name X must land on week Y (I called Set Wk in cell F2).  Note that in my example I only set 1 wk and the list must be in order accordingly.  You could have a week set for each name and then use a lookup but then you must make sure each name has a unique index based on the MOD(week num, count of names).  Here is the formula and a picture of how it is set up and col B is calculated automatically (note i used an Array so if you don't have Excel 365 then remove the array $A$2:$A$53 and use $A2 and then copy/fill down.

=INDEX(OFFSET($E:$E,1,0,COUNTA($E:$E)-1),MOD($A$2:$A$53-$G$2,COUNTA($E:$E)-1)+1)

[#8]

@mtarler thanks so much for your help and your time. Excellent!

It is pretty much what I am after except creating the list.

I was hoping to feed the formula from a specific week number to output the name. E.g.

Cell A1 =WEEKNUM(TODAY(),21)

Cell A2 = formula to display the name for the relevant week number in cell A1

Of course the above would follow the logic set out in your example where I can define a specific week number for the starting name.

I tried a vlookup but it ruins the list you created in A/B columns. What am I missing, I guess its what you mentioned about an unique index?

[#9 · Accepted solution]

@matt0020190  i updated my sample sheet to have a ThisWk cell and a YouPick cell and the corresponding names.  i used the exact same formula as B2 but instead of the array $A$2:$A$53 I just point to the WkNumber of interest.  Note: that formula is 'overly complicated' in that it doesn't assume how many people are in that list.