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

sethk1024

New Member
Joined
Aug 31, 2022
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Attachments

  • Screen Shot 2022-08-31 at 8.43.47 PM.png
    Screen Shot 2022-08-31 at 8.43.47 PM.png
    34.9 KB · Views: 8

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Here is one approach:
MrExcel_20220831.xlsx
ABCDEFGHIJKLM
1PlayerPosYrWkSnap%PlayerYrPosTeam123
2ARqb2019397%AR2019qbGB99%98%97%
3ARqb2020297%AR2020qbGB98%97%96%
4ARqb2021191%AR2021qbGB91%90%89%
Sheet3
Cell Formulas
RangeFormula
E2:E4E2=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))
 
Upvote 0
Solution
How about
Excel Formula:
=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)
 
Upvote 0
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.
 
Upvote 0
Here is one approach:
MrExcel_20220831.xlsx
ABCDEFGHIJKLM
1PlayerPosYrWkSnap%PlayerYrPosTeam123
2ARqb2019397%AR2019qbGB99%98%97%
3ARqb2020297%AR2020qbGB98%97%96%
4ARqb2021191%AR2021qbGB91%90%89%
Sheet3
Cell Formulas
RangeFormula
E2:E4E2=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 :)
 
Upvote 0
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
 
Upvote 0
No need to remove the post from EF, just let them know it's solved.

Did you try the formula I suggested?
 
Upvote 0
We're happy to help...and welcome to the MrExcel forum.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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