Excel Tips to Change Your Life: Index/Match

We are excited to announce that Elizabeth Dodge of Equity Plan Solutions has agreed to author a series of occasional blog entries on Excel, accounting, and other stock plan administration topics. We present the first entry in her series today.

Vacate Vlookup!

By Elizabeth Dodge, CEP, Equity Plan Solutions

If you’ve seen me present on Excel tips and tricks (and I present on Excel a LOT), my first point is nearly always that you should stop using Vlookup and start using Index/Match in its place.

Why does this tip always get top billing? Because, once you master the syntax of Index/Match, it is a much, much better way to look up data using Excel.

What Are the Advantages, You Ask?

1. With Index/Match you can look up from RIGHT to LEFT instead of always having to look up from LEFT to RIGHT. (Really, I should just stop here, because, well, I’ve won the argument. But I’ll go on…)
2. With Index/Match, you don’t have to count columns! When you’re dealing with spreadsheets with a lot of columns, this is a life saver!
3. If you insert a column in the data, Index/Match does not break since you are pointing at specific columns, not pointing at, say, column 11 (which has now become column 12).
4. Hidden columns don’t cause any issues. Again, you are pointing at three columns, not counting columns across your data.
5. In theory, file sizes are smaller, files are faster. I’ve heard it claimed (on the internet, where everything you read is true!) that Index/Match will reduce file size and also increase the speed at which formulas will recalculate.

The downside of Index/Match? The syntax is more challenging. However, once you master it, you will never go back. Even Barbara Baksa, who once scoffed at the complexity of the syntax, has seen the light and now swears by Index/Match.

So… How Do You Use It?

When you use Index/Match, you point at three columns, in this order:

1. Column that you want data from (source tab/spreadsheet)
2. Column that you are trying to match (target tab/spreadsheet)
3. Column that you are trying to match (source tab/spreadsheet)

The syntax is:

=index(ColumnWithData-Source, match(ColumnTryingToMatch-Target,ColumnTryingToMatch-Source,0))

The “,0” on the end tells Excel to find an exact match. You can use 1 or -1 to find the closest match before or after, if that is what you need.

I know, I know, right there you are thinking “Too complicated! Why bother! Vlookup works!” I thought this too. For a long time. Then I practiced. And now I save so much time every time I do a look up that I say that “Index/Match changed my life.” And there are others who feel the same way! I once heard an auditor speak those very words when I was on site with a client.

Here is a screenshot of the formula in use:

Note that:

1. The column we are trying to get data from is Column J.
2. The column we are trying to match to in the location where we want to bring the data is Column A.
3. The column we are trying to match to in the source data is column I.

It did take me a while to get the syntax memorized, so here’s a trick to help you. Think of it as a sandwich: Source, Target, Source. Target is sandwiched by Source.

Still not confident about Index/Match? Check out my video demonstrating how to use it.

Please consider investing the time to learn this formula. If you do, I can almost guarantee that your life in stock plans will be the better for it! 

Elizabeth Dodge is the founder of and a Principal at Equity Plan Solutions, a consulting firm that assists companies with their stock plans, including administration, accounting, and special projects. She has been an active participant in the equity compensation industry since 1998, and became a CEP in 1999.

Elizabeth is a frequent speaker at equity compensation events, past president of the Silicon Valley Chapter of the NASPP, a member of the NASPP, serves on the NASPP Executive Advisory Committee, and a 2012 recipient of the NASPP's Individual Achievement Award.