Can you use a cell as a reference for a table array in a Vlookup formula?

[Oakey]

Im trying to create a daily register for my staff, but each day the teams in will change.

I currently have 7 teams and under these team a list of names.

I have names the teams
AMTeam1
AMTeam2
AMTeam3 etc.

I have the formula that can show the teams that are in on the day but i would like it to show me the name of the staff

In cell B3 and downwards is where i would like the formula, I want the table array to be taken from cell B2 (this will change dependant on the date in cell B1)

Below is the table array that i have called AMTeam1

Any help will be great.

Thank you Stuart

 

[Toadstool]

Hi Stuart,

Your challenge with VLOOKUP is that you want the name of the second Moca for the second Moca but VLOOKUP will keep returning the 1st.

You don't say how the date is used so I've mocked up a shift where the WEEKDAY denotes the shift.

Oakey.xlsx
	A	B	C	D	E
1
2
3		AMTeam1	AMTeam2	AMTeam3	AMTeam4
4	Shift Manager	Mike	Michelle	S3	S4
5	Team Leaders	Terry Uno	Tristan Duo	TL3A	TL4A
6	Team Leaders	Tammy Wan	Trieste Deux	TL3B
7	Moca	Mitch	Mona	Moca3A	Moca4A
8	Moca	Milly	Munch	Moca3B	Moca4B
9	Moca	Mandy		Moca3C	Moca4C
10
Teams

Oakey.xlsx
	A	B	C	D	E
1	Date	03-Jan-22	04-Jan-22	05-Jan-22	06-Jan-22
2		AMTeam1	AMTeam2	AMTeam3	AMTeam4
3	Shift Manager	Mike	Michelle	S3	S4
4	Team Leaders	Terry Uno	Tristan Duo	TL3A	TL4A
5	Team Leaders	Tammy Wan	Trieste Deux	TL3B
6	Moca	Mitch	Mona	Moca3A	Moca4A
7	Moca	Milly	Munch	Moca3B	Moca4B
8	Moca	Mandy		Moca3C	Moca4C
9
Shifts

Cell Formulas
Range	Formula
C1:E1	C1	=B1+1
B2:E2	B2	="AMTeam"&WEEKDAY(B$1,2)
B3:E9	B3	=IFERROR(INDEX(Teams!$B$4:$E$99,AGGREGATE(15,6,ROW(Teams!$A$4:$A$99)-ROW(Teams!$A$3)/((Teams!$A$4:$A$99=$A3)),COUNTIF($A$2:$A3,$A3)),MATCH(B$2,Teams!$B$3:$E$3,0))&"","")

 

[Oakey]

Thank you for your reply.

What if i was to change the titles on the left, so it read something like this?

Its the team name that i would like to get into the Vlookup some how, but when it changes i want the formula to also change, is this something that is possible?

A bit more information, i have 7 teams and each day i will have atleast 4 teams in, sometimes 5 and once a week 6.
Id like to change the day and a sheet to appear that would show just the selected trams that are in (names included)

Thanks Stuart

 

[RoryA]

Assuming it's a static named range, you can use INDIRECT(B$2) to get a reference to it.

 

[Toadstool]

If you really want to use VLOOKUP then yes, you could add a number to duplicate roles, define the whole Staff table as a single table then use COUNTIF to select which role and the Team number to select which column.

Oakey-v2.xlsx
	A	B	C	D	E
1
2
3	Staff	AMTeam1	AMTeam2	AMTeam3	AMTeam4
4	Shift Manager	Mike	Michelle	S3	S4
5	Team Leader 1	Terry Uno	Tristan Duo	TL3A	TL4A
6	Team Leader 2	Tammy Wan	Trieste Deux	TL3B
7	Moca 1	Mitch	Mona	Moca3A	Moca4A
8	Moca 2	Milly	Munch	Moca3B	Moca4B
9	Moca 3	Mandy		Moca3C	Moca4C
10
Teams

Oakey-v2.xlsx
	A	B	C	D	E
1	Date	03-Jan-22	04-Jan-22	05-Jan-22	06-Jan-22
2	Teams	1	2	3	4
3	Shift Manager	Mike	Michelle	S3	S4
4	Team Leader	Terry Uno	Tristan Duo	TL3A	TL4A
5	Team Leader	Tammy Wan	Trieste Deux	TL3B
6	Moca	Mitch	Mona	Moca3A	Moca4A
7	Moca	Milly	Munch	Moca3B	Moca4B
8	Moca	Mandy		Moca3C	Moca4C
9
Shifts

Cell Formulas
Range	Formula
C1:E1	C1	=B1+1
B3:E9	B3	=IFERROR(VLOOKUP($A3&IF(COUNTIF($A$3:$A$99,$A3)>1," "&COUNTIF($A$2:$A3,$A3),""),Staff,1+B$2,FALSE)&"","")

Named Ranges
Name	Refers To	Cells
Staff	=Teams!$A$3:$E$9	B3:E9

 

[Oakey]

Assuming it's a static named range, you can use INDIRECT(B$2) to get a reference to it.

Thank you.

How would the formula look like?