Allow me just one moment to geek out with the community.

My hobby over the past several years, and what has now morphed into my full time job, is data analysis. At first it was just a hobby with fantasy sports. Eventaully, I became a go to guy in my school as a data aggregator and analysis dude. Now that has morphed into a full time job, with me being asked to sift through ungodly amounts of educational related information and with really quick turnaround times.

So, in a high pressure world with the expectation of pretty quick results, I need at least a few tricks up my sleeve. Allow me to share my favorite:

INDEX(MATCH()), Nectar of the Spreadsheet Gods

Look, I know people hate spreadsheets. There was a time when I used to as well. And, I've since moved on to learning SQL and R where life is but a data filled dream.

But, every now and again when it is more convenient to just get to a conclusion quick, I always come back to my best data friend. And that is INDEX(MATCH()).

Here's the Situation - Looking up Fantasy Football Stats

Say I have a database of information. Say it has a bunch of stuff. Maybe that database looks like this:

Okay, so I love fantasy NFL football. I have sheets like this on every player that has played at least 1 game since 2003. This one just happens to be for Roddy White, Wide Reciever for the Atlanta Falcons. It displays his historical game stats, opponents, week played, and the percentile of his position he was in that week. So, you know, stuff only dorks like me care about.

Often, I need to pull really quick information from these sheets and need to get that information instantly. So, say I REALLY need to know how many yards receiving Roddy White had in Week 14 of the 2014 NFL season. Of course, I could just look that up manually like a stupid human:

But, after all, I'm a stupid human and want the computer to just do this for me.

Strategy #1 - VLOOKUP()

Sometimes, when I am slumming it, I will use a function in Excel called VLOOKUP. Here's the logic behind it.

I tell Excel the column I want it to find something. It will then Vertically look up and down that column until it finds what I want it to find. Then, once it does what I tell it to do it will look across that row in the place I tell it to find what I need it to find.

Here's what that command would look like:

=VLOOKUP("14WeekATL",$F$2:$R$29,9,FALSE)

"14WeekATL" is what I want Excel to find by looking vertically up and down what is in the range $F$2:$R$29
"14WeekATL" is in quotes because it is a "string", or in normal terms, text.
$F$2:$R$29 iis the blue selection you see above. The dollar signs are called an absolute reference, which means I only want the computer to look at that range only forever and never mess with it.
9 means I want Excel to find what is 9 entries over to the right, counting what is in the season column as number 1.
FALSE just means I want an exact match. So, FIND WHAT I WANT YOU TO FIND EXCEL!

Notice the desired result:

Strategy #2 - INDEX(MATCH())

Ahh...like cold water on a warm Summer day. So refreshing, and you intiutively just know its amazing.

VLOOKUP is always cool to use, but is kind of annoying for two reasons. First, you have to count the number of columns over you want to find a desired entry. And really, who has the time for that?

Second, VLOOKUP only works if you are trying to find something to the RIGHT of the vertical column you identify. If you try to find something to the left? Like the opponent in Week 14 for Atlanta? You know, stuff to the LEFT of the vertical column? Maybe something like this?

=VLOOKUP("14WeekATL",$F$2:$R$29,-1,FALSE)

Whoops! VLOOKUP() don't play that!

Enter INDEX(MATCH()). To think like INDEX(MATCH()), you have to think like Yoda. Talk in reverse you must...hmmm!

To fix the reverse issue with VLOOKUP, just tell excel the column you desire to INDEX, and it will then MATCH the entry you are looking for.

Here's what that command would look like:

=INDEX($D$2:$D$29,(MATCH("14WeekATL",$F$2:$F$29,0)))

INDEX is the range where I want Excel to find my answer. It will be in the column that is between $D$2:$D$29
MATCH is what I want Excel to find for me. Again, it will be "14WeekATL".
$F$2:$R$29 is the red selection you see above. It is where the computer will MATCH "14WeekATL" .
0 means I want Excel to find an exact match to "14WeekATL" .