Looking for Creative Use of INDEX MATCH formulas for my specific scenario

[sethk1024]

ok so as you can see in the image, theres two tables

table on the left is the stat table for a given NFL player. (I hid all the stats for simplicity of viewing). The columns you see are player name, year, and week. The output i'd like to generate is "Snap %", which is the number of plays the given player played in a given game we are recalling based on the player, year and week columns.

the table on the right, is the table we are recalling snap %s from. the "number" headings represent a given week. 1 = Week 1 of the season, 2 = Week 2 of the season, etc, and the numbers underneath that are the Snap %s for the game that week.

An entire example would read.... If I wanted to know what % of the snaps Aaron Rodgers played in 2019 Week 2, it would be 99%.

Now, I know how to use INDEX MATCH formula, even with multiple criteria. The issue for me is, I don't know how to apply this formula in this specific situation because the output / result portion of the formula (if there was just a single week for snap %, it would be very easy), however, because the Weeks we are recalling can always change, it creates confusion for me.

Example:

variables:

player: Aaron Rodgers
year: 2019
OUTPUT snap %: [average snap % for season]

The above is easy


however, I need:

player: Aaron Rodgers
year: 2019
Snap % for week 1: [result]

player: Aaron Rodgers
year: 2019
Snap % for week 2: [another result]


This is confusing me and if you can help me I'd GREATLY appreciate it. Thank you!

 

[KRice]

Here is one approach:

MrExcel_20220831.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Player	Pos	Yr	Wk	Snap%		Player	Yr	Pos	Team	1	2	3
2	AR	qb	2019	3	97%		AR	2019	qb	GB	99%	98%	97%
3	AR	qb	2020	2	97%		AR	2020	qb	GB	98%	97%	96%
4	AR	qb	2021	1	91%		AR	2021	qb	GB	91%	90%	89%
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=INDEX($K$2:$M$4,MATCH(1,($G$2:$G$4=A2)*($H$2:$H$4=C2),0),MATCH(D2,$K$1:$M$1,0))

 

[Fluff]

How about

=FILTER(FILTER($K$2:$Z$100,($G$2:$G$100=A2)*($H$2:$H$100=C2)*($I$2:$I$100=B2)),$K$1:$Z$1=D2)

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Looking for Creative Use of INDEX MATCH formulas for my specific scenario
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[sethk1024]

Here is one approach:

MrExcel_20220831.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Player	Pos	Yr	Wk	Snap%		Player	Yr	Pos	Team	1	2	3
2	AR	qb	2019	3	97%		AR	2019	qb	GB	99%	98%	97%
3	AR	qb	2020	2	97%		AR	2020	qb	GB	98%	97%	96%
4	AR	qb	2021	1	91%		AR	2021	qb	GB	91%	90%	89%
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=INDEX($K$2:$M$4,MATCH(1,($G$2:$G$4=A2)*($H$2:$H$4=C2),0),MATCH(D2,$K$1:$M$1,0))

THIS IS *PERFECT*!!

Thank you so much

 

[sethk1024]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Looking for Creative Use of INDEX MATCH formulas for my specific scenario
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

sorry, new to excel question forums. i will remove the other post considering this one has a solution. cheers

 

[Fluff]

No need to remove the post from EF, just let them know it's solved.

Did you try the formula I suggested?

 

[KRice]

We're happy to help...and welcome to the MrExcel forum.

 

[sethk1024]

No need to remove the post from EF, just let them know it's solved.

Did you try the formula I suggested?

I didnt – the first commenter's formula worked perfectly so I just rolled with that. I've bookmarked your comment though so I can come back to it and test if necessary