Not even sure if this is possible?

[Zoe B]

Hi,

im trying to create a roster in excel, but wondering if i can get the name of the person on call to auto populate (C7) from a number in the rostered week (G7-M7)
the list is on another tab in the workbook (shown)
Also wondering if it can open on todays date (redline with formula in C2) and show those names only

Apologies if that doesnt make sense. we have 18 teams/roles on call each unfortunately with differing numbers of people in those teams. the roster cycles every 8 weeks, but some teams have more than 8 people, hence not easy to reflect.

 

[KRice]

I'm not quite sure what you want, but here is an approach that matches the role against the roster headings to find the relevant column (in the roster) and then matches the number (taking the max number found in the block of 7 cells) against the roster column A number to return the person's name.

Book1
	A	B	C	D
1	Number	CICC Leader	Backup	Duty Manager
2	1	XX	CC	ZZ
3	2	YY	GG	BB
4	3	ZZ	FF	CC
5	4	AA	XX	GG
6	5	BB	YY	XX
Roster

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	CICC Roster
2	Start		3/28/2022
3	Display Wk		1
4				3/28/2022							4/4/2022
5
6		Role	Name	M	T	W	T	F	S	S	M	T	W	T	F	S	S
7		CICC Leader	YY									2
8		Duty Manager	CC						3
9		Backup	XX							4
10
Sheet2

Cell Formulas
Range	Formula
C7:C9	C7	=XLOOKUP(MAX(G7:M7),Roster!$A$2:$A$6,INDEX(Roster!$A$2:$D$6,,MATCH(B7,Roster!$A$1:$D$1,0)))