how to use index match with multiple tabs

dw300

New Member
Joined
Feb 24, 2016
Messages
8
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"
1PLAYER 1CLASS
2020​
PassingRushingReceivingScoring
RB/DB6-0185CompAttYardsTDIntAttYardsAvg. TD2ptRecYardsAvg.TD2pt.TD2ptXPFGTotal
PLAYER 1FR2017
PLAYER 1SO2018
PLAYER 1JR2019
PLAYER 1SR2020

"2017"
2017PassingRushingReceivingScoring
SEASON STATSCompAttYardsTDIntAttYardsAvg. TD2ptRecYardsAvg.TD2pt.TD2ptXPFGTotal
1PLAYER 1FR2017000006254.200199.00000000
2PLAYER 2SO20173067402162168.000000.00000000
4PLAYER 4SO2017000008364.5001215813.21010006
6PLAYER 6FR201700000000.0001723513.80000000

"2018"
2018PassingRushingReceivingScoring
SEASON STATSCompAttYardsTDIntAttYardsAvg. TD2ptRecYardsAvg.TD2pt.TD2ptXPFGTotal
1PLAYER 1SO20180000022934.2101521714.530400024
2PLAYER 2JR20181222781353814431242.960000.000600036
4PLAYER 4JR20180000014574.1104746810.030400024
5PLAYER 5FR20180000045914.8001721612.70000000
6PLAYER 6SO201800000122.0004345210.520200012

OUTPUT NEEDED IN "CAREER STATS" FOR PLAYER 1
1PLAYER 1CLASS
2020​
PassingRushingReceivingScoring
RB/DB6-0185CompAttYardsTDIntAttYardsAvg. TD2ptRecYardsAvg.TD2pt.TD2ptXPFGTotal
PLAYER 1FR2017000006254.200199.00000000
PLAYER 1SO20180000022934.2101521714.530400024
PLAYER 1JR201900000856737.9803549814.2301100066
PLAYER 1SR2020
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
793
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2016
Messages
8
=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

Well-known Member
Joined
Mar 22, 2015
Messages
793
Office Version
  1. 2013
Platform
  1. Windows
Ah
sorry
Try
=INDEX(INDIRECT("'"&$D4&"'!"&"$B$4:$X$7"),MATCH($C4:$C$7,INDIRECT("'"&$D4&"'!"&"$c$4:$c$7"),0),COLUMN()+1)

<<; ,>>
 

dw300

New Member
Joined
Feb 24, 2016
Messages
8
Tested this quickly and appears to be working. Thank you very much for your assistance!!
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
793
Office Version
  1. 2013
Platform
  1. Windows
You are well come and thank you for the feed back
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,118,972
Messages
5,575,302
Members
412,654
Latest member
dibison18
Top