The leading association for the stock and executive compensation profession
Join a professional community 6,000+ members strong
Continue your investment in your professional development and community
Our member care center is here to assist you
Country-specific guidance for stock plan design and administration
Connect with a chapter in your area
Learn more about and engage your peers
Attend an NASPP event for unbeatable professional development and networking
Ask, find and provide answers to burning industry questions
Professional development to keep you at the top of your game
Expert industry perspectives and guidance for your daily work
Enrich you career and discover new opportunities
Be there for the 27th annual event Sept. 16-19, 2019 in New Orleans!
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.
How Much Are Stock Plan Administrators Paid?
Members occasionally ask me for salary or compensation benchmark data for stock plan administrator roles. For today’s blog entry, I thought I’d summarize some sources for this d...Read More
Alexa: Sell My ESPP Shares!
We’re living in an era of digital disruption that affects most (if not all) industries, where technology evolves so quickly that we barely have time to figure out the sta...Read More
Beneficiary Blunders: Aligning Participant Intent with Plan Technicalities
The arrival of a new year signifies several things – for some it’s a time of resolutions and goal setting. For others, it’s more of an “out with the old, in with the new&rdq...Read More
A Government Shutdown Doesn’t Shut Down Your Stock Plan
We are now entering the third week of the government shutdown, just as work in corporate America is getting back into full swing after the holidays. You might be wondering how this will impact v...Read More
Year End Checklist: 3 Things to Add
You may be performing your year-end tasks off of a checklist – after all, year-end only happens once every 12 months. It’s easy to forget everything you did 52 weeks ago to complete one...Read More