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.
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.
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.
When you use Index/Match, you point at three columns, in this order:
The syntax is:
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:
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.
5 Work from Home Hacks
I’ve been working from home for about 15 years, so when the scenario of a national work-from-home response to COVID-19 surfaced, I wasn’t initially fazed too much. Then, r...Read More
Year End Checklist: 5 Things to Add
Whether or not you have a formal checklist to manage year-end tasks, undoubtedly a list exists somewhere – even if it’s only in your head (if that’s the case, though, I’d re...Read More
Year-End Resources for Administrators and Participants
With the end of the year approaching, it’s time for me to highlight some of the resources available to get you and your stock plan participants smoothly through year-end planning and processe...Read More
Insider Trading: Would You Sell Your Career for $100,000?
I’ve followed the topic of insider trading over many seasons and SEC enforcement actions, and the one thing that I probably find most fascinating is the “who” becomes entangled...Read More
Four Essential Traits of the Future Stock Plan Professional
I’m still riding the high of exuberance that I felt last week at our 27th Annual NASPP Conference in New Orleans. One of our best ever, there was a sense of excitement everywhere I...Read More