The Excel Formula I Can't Live Without
The Excel Formula I Can't Live Without
Contributor:
Jen McDonald, Georgian College
What defines the pinnacle of being a nerd, you might ask. For me, the ability to have in-depth discussions about Excel and the intricacies of finding the perfect formula, would be up there.
Before I began my role in the ONEcard Office, I had a basic understanding of Excel and primarily used it for data tracking and quick stats. Once I joined the card office, and now needed to add accounting tasks to my list of responsibilities, I quickly realized the value of upping my skill level in all things Excel.
However, none of what I learned can compare to the value I have gained from one (relatively) simple formula, the VLOOKUP. If you’re already familiar with this little gem, then I likely don’t need to tell you how helpful it can be. But if you number among the unfortunate masses who have never heard of this formula, let me enlighten you.
I find myself frequently, in fact on an almost daily basis, needing to combine data from two different sets. A few examples would be needing to compare a list of registered students to a list of accounts with balances, or perhaps you want to compare data in your test environment to that of PROD. How does one accomplish such a task while maintaining their sanity? VLOOKUP, my friends.
While I will not go into a tedious and long-winded explanation of how the formula works (there are many great resources out there to learn from), I will offer some tips for ensuring the formula co-operates to the best of its ability. These may not be essential, but after a few issues with early attempts at VLOOKUP, I found the following usually resolved any problems I was having.
- Make sure that the identifier you have chosen is converted to the appropriate format. This can be done by selecting the column, then selecting “Text to columns” in the Data tab.
- Ensure that the identifier in each table is in the same column location. For me, I always have it in the first column. For example, if you’re using student/account number because it exists in both sets of data, have the student/account numbers in column A in both tables.
- After selecting all of the data in the second table, be sure to press F4. This establishes that you want to check the entire table, and not just the area relative to where the original line occurs.
Do you have an Excel formula that you simply could not live without?
I would love to hear about it! Okay, maybe love is a little strong, but I would certainly find it interesting. Feel free to let me know.
VIDEO TUTORIAL:
HOW TO USE VLOOKUP