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.
Implementing an Auto Exercise Program for Stock Options
Companies that grant stock options know that there are a few core challenges that have maintained their existence throughout the life span of these types of arrangements. Among them: how to handle ...Read More
Is Spousal Consent Necessary for Equity Awards?
Occasionally, I am asked if it is necessary for employees’ spouses to accept the equity awards granted to employees, in addition to the employees’ acceptance. In my experience, ...Read More
Three SEC Updates to Keep an Eye on
For today’s blog entry, I discuss a few recent and upcoming securities law developments.
Last year, the SEC issu...Read More
Growing Your Career in Stock Administration
Whether you’re new to the field of equity compensation or have been with us for a while now, there’s always room to grow. While much of our time is spent staying on top of new d...Read More
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