Google Sheets Tutorial for Sports Science: INDEX and MATCH Superpowers (Multiple Criteria)
ฝัง
- เผยแพร่เมื่อ 19 ก.ย. 2024
- For more tutorials, go here:
This is a video designed for sports scientists, sport and strength coaches, and other practitioners working in sport on how to get the most out of Google Sheets for sports science and performance.
We go through how to use the formulas INDEX and MATCH in an advanced way whereby we match on a row using multiple criteria... AND a column. This allows us to dynamically display information for both an athlete-date combination (row) and metric (column) of interest.
This concept could be used to dynamically display performance data, including sprint times, jump heights, training load measures, and in-game performance metrics in a dashboard whereby the user interacts with the data by selecting an athlete and metric of interest.
You got a new fan, tks
Thank you my friend!
Greatly appreciated sir! This unlocks a knowledge gap of mine as I build out my own client training worksheet. Really appreciate you specificity and real life applications in the training field.
Any formula pointers to use if I want the date column to be dynamic also so I can populate the last time a client had data in a respective field? Often times I don’t know the date of the data I’m seeking but want to see where the data for said exercise (or sleep quality, RPE, etc…) was during the clients last visit (or last 3 visits).
Hi Blake. Thank you for your kind words, and this is a great question.
The first step I take is finding the dates with the most recent data. You can use either MAXIFS or MAX(FILTER( to get the maximum date for the client of interest. Or, if you want the last 3 sessions, you could use SORTN(FILTER( to get highest X number of dates for that client.
I hope this helps!
Hi there! Very helpful video! I've got a problem. When I edit the data cells instead of updating the indexes and matches i get errors like #VALUE! and #N/A "Function INDEX parameter 3 expects number values. But 'Player 6' is a text and cannot be coerced to a number." or "Did not find value '9' in MATCH evaluation." Any solution?
Hi! I can't provide a solution without seeing the data set, unfortunately. There could be various things affecting what you're trying to do. Sorry that this isn't very helpful. As a first step, I would encourage you to try the formulas using a data set that is similar to mine (if not exactly the same) and then replacing that information with yours.