Where I was when I learned INDEX-MATCH
I came across a job posting recently that listed in the requirements "You remember where you where when you learned INDEX-MATCH"
And you know what? I DO remember where I was when I learned index-match. It was surprisingly recent, maybe a couple of months ago.
See, one of my most deeply held convictions has been that INDEX-MATCH is an overly complex, outdated, waste of time that's only used by people who think they are good at Excel but haven't yet bothered to learn XLOOKUP.
I have held this conviction for years. Getting into arguments with senior team members, friends, and my own mother. Am I out of touch? No, it is the children who are wrong.
But I was about to be defeated, I was broken by the College Football 25 Dynasty tracker from hell.
For those out of the loop, INDEX-MATCH is a lookup method in Excel, it lets you feed in a search value and where to look for it, and returns an associated value. The problem with index-match is that it's complicated - it's technically two formulas that you have to nest together, and generally requires that you know the column or row number in the data you're searching.
If I'm working through a spreadsheet of customer data with 65 columns I should not have to count out which column "Address" is in.
XLOOKUP does a very similar thing. You tell it the search value, where to lookup the search value, and the column that the return value is in. XLOOKUP is chill because it's one function that only asks you for three things and none of them are a column number.
I started an online CFB 25 dynasty with some friends - basically we all pick college football teams, manage them year over year, and compete with each other. Because I am a sicko who makes spreadsheets for fun, I wanted to challenge myself and make the ultimate tracker for us. I had to be three things:
- Nicely formatted
- Easy for me to input data
- Able to track and display data from previous weeks, not just the current week
Nicely formatted? Easy peasy lemon squeezy. I had a lot of fun playing with cell widths and fonts - I also stole a handy reference sheet from another CFB tracker I found with all of the logos and team info in it that I could use to pull in logos.
Easy for me to input data? Ehhhhhhhh medium peazy orange juicy.
Rankings was pretty straightforward. Columns for week over Week, rows for the rank. I go to the rankings tab and write in the teams that ranked - 30 seconds bing bam bop.
Schedules were harder - because my brain has been rotted by years of doing crackhead calculations on sales data, I set it up so that every teams game was an individual row, even if that meant duplicating instances of games. (Georgia at Alabama in Week 12 has two entries, one for UGA and one for BAMA). I did this mostly because it made it really easy to count Wins/Losses for each team.
I used a combo of XLOOKUP + FILTER to get all of the away teams for that week, and then used that to find the home team - nice. we did it guys.
Turned this...
Into this...
Look at data week over week.
This is where it gets painful. Extra stressy lemon zesty, if you will. Because I didn't use any sort of traditional data structure for my score input, I had to turn to index-match. My nemesis.
I needed our input table to fill the data into this template AND I needed it to change whenever I chose another week - so if I wanted to look at Week 5, or Week 7 - all of the data will change.
The listed teams and scores are all found via INDEX-MATCH, and the logos and team locations are XLOOKUPs with an INDEX-MATCH nested inside of them. I couldn't use a normal XLOOKUP here because I also have logic that adds a ranking number if the team is ranked, which would break a lookup against the reference sheet.
--
My spreadsheet is done. It's pretty. It's easy to use.
I, the hater, have been defeated. INDEX-MATCH has won.
SIKEEEEEE!!!!!!!
We're gearing up for Season 2 of our dynasty and so I revisited this spreadsheet to clean it up and get it ready for new data.
As I was going through it I thought "What the f*** was I thinking? Was I high when I designed this? Am I an idiot?"
I made this NEEDLESSLY complicated in round one. So for round two I redesigned the scheduling database and matchups input. One entry for each scheduled game (instead of 2), and instead of entering the scores on a separate sheet from schedule db, I can just enter them directly.
(Here's what it looks like now)
Then I got rid of every single INDEX-MATCH on this sheet. (Ok, not reallyI still use INDEX just not MATCH and I still have to use INDEX-MATCH on the rankings table but I'm going to pretend that it sucks and that I was right all along).
What's the moral of this story?
There's a lot of possible options here:
- Max is always right (a classic)
- XLOOKUP is superior to INDEX-MATCH (duh)
- I desperately need new hobbies (but my fragile hands can't handle woodworking so spreadsheets it is)
But ultimately, I think the answer is "don't build bad databases" - there is a lot of energy put into the spreadsheet because I insisted on some really dumb data entry and database designs early. I just made it way harder than it needed to be.
If you wanna take a look - I've made both spreadsheets available here:
V1: https://docs.google.com/spreadsheets/d/1r8_dsnHsZIWvx1JyGCQHyJb4BGD5ytzDnmj_kxwNZHg/edit?usp=sharing
V2: https://docs.google.com/spreadsheets/d/1L_mzljrMStCyAyJk4amit5MtbDCtIk5QA1CBLK6fRhg/edit?usp=sharing