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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
=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.
 
Upvote 0
Ah
sorry
Try
=INDEX(INDIRECT("'"&$D4&"'!"&"$B$4:$X$7"),MATCH($C4:$C$7,INDIRECT("'"&$D4&"'!"&"$c$4:$c$7"),0),COLUMN()+1)

<<; ,>>
 
Upvote 0
Tested this quickly and appears to be working. Thank you very much for your assistance!!
 
Upvote 0
You are well come and thank you for the feed back
Be happy
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top