Looping through a list of name each new week

[kaufa]

Hi all,

Hope to be able to find some help : thanking you in advance !
First of all, if possible, I would like to avoid any VBA, but am ok to use it if no other options.

Here is my problem :
I have a list of names in A1:A5 and the current week number showing in B1.
My goal is to loop through the A1:A5 range and show the next name in the list each new week in cell C1.
So far I have failed to make it work, trying to use IF formula.

Thank you very much in advance for your support !

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(A1:A5,MOD(B1-1,5)+1)

 

[kaufa]

Works like a charm, thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.