how to use index match with multiple tabs

[dw300]

I need to pull data from multiple tabs into one main tab using index match.

I am football statistician for local high school. Trying to create a "career stats" sheet for all players. I have separate stat sheets for each season "2016","2017","2018","2019", ect. I need to pull each players stats from each yearly sheet for every statistical category. In the example sheets below, in my "career stats" tab, I enter the player name in cell B2 and then the year of graduation in cell D2. Once the year of graduation is entered, the seasons (year) are automatically calculated from FR to SR in cells (D4:D7).

For "PLAYER 1" (B2), his freshman (FR) year was 2017 (D4). When the result in cell "D4" returns 2017 (=D2-3), I need the "Career Stats" sheet to find "Player 1" from sheet "2017" and then return the stats for each statistical category (E3:S3)and return results in (E4:S4). Likewise, "D5" returns 2018, therefore, the career sheet needs to pull from sheet "2018", ect. I don't know if it's possible to use the result in column D in order to find the corresponding sheet and the Player. My hope is that when the result in column D of the "Career Stats" sheet changes the year, that it knows automatically to use that sheet to find the result. I would like to only enter the "player name" in "B2" and year of graduation in "D2" and everything else calculates automatically.

In the attached example, I have pasted the results that are needed to be returned. If possible to post the formula for cell "K4" that will return "25" and "P5" that will return "217", it would be greatly appreciated.

"CAREER STATS"

1

PLAYER 1

CLASS

2020​

Passing

Rushing

Receiving

Scoring

RB/DB

6-0

185

Comp

Att

Yards

TD

Int

Att

Yards

Avg.

TD

2pt

Rec

Yards

Avg.

TD

2pt.

TD

2pt

XP

FG

Total

PLAYER 1

FR

2017

PLAYER 1

SO

2018

PLAYER 1

JR

2019

PLAYER 1

SR

2020

"2017"

	2017			Passing					Rushing					Receiving					Scoring
	SEASON STATS			Comp	Att	Yards	TD	Int	Att	Yards	Avg.	TD	2pt	Rec	Yards	Avg.	TD	2pt.	TD	2pt	XP	FG	Total
1	PLAYER 1	FR	2017	0	0	0	0	0	6	25	4.2	0	0	1	9	9.0	0	0	0	0	0	0	0
2	PLAYER 2	SO	2017	30	67	402	1	6	2	16	8.0	0	0	0	0	0.0	0	0	0	0	0	0	0
4	PLAYER 4	SO	2017	0	0	0	0	0	8	36	4.5	0	0	12	158	13.2	1	0	1	0	0	0	6
6	PLAYER 6	FR	2017	0	0	0	0	0	0	0	0.0	0	0	17	235	13.8	0	0	0	0	0	0	0

"2018"

	2018			Passing					Rushing					Receiving					Scoring
	SEASON STATS			Comp	Att	Yards	TD	Int	Att	Yards	Avg.	TD	2pt	Rec	Yards	Avg.	TD	2pt.	TD	2pt	XP	FG	Total
1	PLAYER 1	SO	2018	0	0	0	0	0	22	93	4.2	1	0	15	217	14.5	3	0	4	0	0	0	24
2	PLAYER 2	JR	2018	122	278	1353	8	14	43	124	2.9	6	0	0	0	0.0	0	0	6	0	0	0	36
4	PLAYER 4	JR	2018	0	0	0	0	0	14	57	4.1	1	0	47	468	10.0	3	0	4	0	0	0	24
5	PLAYER 5	FR	2018	0	0	0	0	0	4	59	14.8	0	0	17	216	12.7	0	0	0	0	0	0	0
6	PLAYER 6	SO	2018	0	0	0	0	0	1	2	2.0	0	0	43	452	10.5	2	0	2	0	0	0	12

OUTPUT NEEDED IN "CAREER STATS" FOR PLAYER 1

1	PLAYER 1	CLASS	2020​	Passing					Rushing					Receiving					Scoring
	RB/DB	6-0	185	Comp	Att	Yards	TD	Int	Att	Yards	Avg.	TD	2pt	Rec	Yards	Avg.	TD	2pt.	TD	2pt	XP	FG	Total
	PLAYER 1	FR	2017	0	0	0	0	0	6	25	4.2	0	0	1	9	9.0	0	0	0	0	0	0	0
	PLAYER 1	SO	2018	0	0	0	0	0	22	93	4.2	1	0	15	217	14.5	3	0	4	0	0	0	24
	PLAYER 1	JR	2019	0	0	0	0	0	85	673	7.9	8	0	35	498	14.2	3	0	11	0	0	0	66
	PLAYER 1	SR	2020

 

[mohadin]

E2=INDEX(INDIRECT("'"&$D3&"'!"&"$B$3:$X$6");MATCH($C3:$C$6;INDIRECT("'"&$D3&"'!"&"$c$3:$c$6");0);COLUMN()+1)
Drag down and left

 

[dw300]

=INDEX(INDIRECT("'"&$D3&"'!"&"$B$3:$X$6");MATCH($C3:$C$6;INDIRECT("'"&$D3&"'!"&"$c$3:$c$6");0);COLUMN()+1)

Thank you for the response.

When pasting the formula into the cell, I'm getting the following error message

"There's a problem with this formula" ... It is directing me to fix the formula between the ) and the ; ...X$6") __ ;MATCH

Also, I pasted the formula in E3, as that is the first cell to be calculated.

 

[mohadin]

Ah
sorry
Try
=INDEX(INDIRECT("'"&$D4&"'!"&"$B$4:$X$7"),MATCH($C4:$C$7,INDIRECT("'"&$D4&"'!"&"$c$4:$c$7"),0),COLUMN()+1)

<<; ,>>

 

[dw300]

Tested this quickly and appears to be working. Thank you very much for your assistance!!

 

[mohadin]

You are well come and thank you for the feed back
Be happy