In my last entry, we discussed how to write a formula, and you've been armed with what each piece of the formula represents (the command, the variables, and the definition of an array). With this knowledge, you've actually been armed with the keys to the kingdom, and you're finally going to learn how to do something fancy.

There comes a time in every person's life where just by learning one piece of information, suddenly everything else you've learned clicks into place. For those who utilize Excel, learning this formula WILL BE THAT FOR YOU. In our last entry, I stated very plainly that there are only a handful of formulae that are used on a regular basis, and this is easily the most useful and the most flexible, to the point in which knowing how to use this formula (along with a tool that we'll discuss next time) are the SOLE job skills required to get an entry-level job in finance note...put this somewhere on your resume.

What is this mythical savior of white-collar workers worldwide? In the past, you may have heard muffled whispers of "v-lookup," or if your data was poorly formatted, perhaps even "h-lookup." However, after 2007, the minds behind the spreadsheets threw both of those formulae into a blender, mixed it with Redbull and amphetamines, and spit out the king of functions: the =Index(Match)).

So, after all that incredibly well-deserved hype, what is index-match, and why is it so special? To put things incredibly simply, index-match checks (or, as the cool kids say: "bump-up against" one piece of information (say, a list of ID numbers) against another piece of information (say, a second list of IDs) and spits out the data associated with the matching values (Say, the name associated with that ID number). The reasons for the importance of these commands can not be overstated, as anyone who works with data in any capacity knows that identifying changes (and where the changes are) over different iterations of a particular dataset is an essential job function.

Now, hopefully you're starting to see the light, and understanding the power that this formula holds, so now we're going to learn how to use it.

. Still, together
First things first, if you're a particularly perceptive reader, you may have guessed from how I wrote the formula that =Index(Match)) is actually the combination of two separate formulae that alone are of limited use. Still, together, they allow you to accomplish great things. These two formulae are:

1. INDEX which asks to look at an array (essentially a list of cells) and pull out the data at a specific row and column number within that array (interesting fact: despite using letters for columns and numbers for rows, Excel actually translates the column letters into a number with 1=A and so on). Index by itself isn't super useful, as, much like when you're viewing a particle or cooking Meth, you rarely know both dimensions of a cell that you're looking for (that was a Heisenberg uncertainty principle joke...or a Breaking Bad reference, depending on how you roll.)
2. MATCH is the formula that does most of the heavy lifting. Match checks to see where a value exists within an array that you specify and returns the position in the array (for example, if you match "C" on a list of letters of the Roman alphabet, match will tell you "3").

Now, here's where the magic happens. Remember when I said that everything in Excel is basically just a collection of numbers? Well, when a formula asks you for a number, there's no reason why you can't throw in ANOTHER formula that spits out a number. As such, if you tell Index where you want to grab something from, and then instead of giving it a position, you give it the number that pops up from Match (which is a formula that gives you the location of where something matches) you've given Index all the information it needs to do its work, all without needing to know where (or if at all) the value exists.

The formula in practice is written like this:

**=INDEX(*column or row where you want to grab the information from*,MATCH(*the cell containing the value that you want to look for*,*column or row where you want to check for the value*,0)) (don't forget the zero at the end, that just means you want to match the value exactly)

As such, if you have the following data

Sheet 1

Sheet 2

Suppose you want to return the name associated with any numbers in sheet 2. In that case, you can plug the column containing the names in the Index, which contains the value you want to find the associated name with as the first part of the match. The column containing the IDs associated with the names as the second part of the match, the formula will spit out the name (or an error stating that the value doesn't match a name). The best part is this formula can get dragged down the entire list of numbers to return the names associated with all IDs. Now, this is with a very small data set, but it works just as well and as quickly if you're looking at 10,000 IDs.

With that being said, I hope you've learned the answer to a question you didn't even know you had, and I hope index-match starts saving you time (or snagging you a promotion!) immediately.

In summary, we've learned the following:

1. The function of INDEX.
2. The function of MATCH (protip: if all you want to do is see if something matches and don't need to see what it's associated with, you can use match by itself, is =ISNUMBER(Match)) which will give you a nice true-false
3. How can you utilize a formula as a variable in another formula? (You don't need to put in a second equals sign if you're doing this, but you do need another set of parenthesis.)
4. How to write an Index(Match)) formula.
5. The main use for Index-Match (by thinking outside the box, there are an infinite number of uses.)
6. The realization that by knowing this formula, you never need to compare things by hand again and eye again!

Next time, we'll discuss the other frequently used Excel tools that, together with the powers of Index-Match, literally make up the entry-level analyst's toolkit. Also, as a bonus entry in the interim, you'll get a quick rundown of leveraging your knowledge of Index-match in a conversation with someone who knows what they're talking about.

In Hoc Signo,

-Snacks