VLOOKUP is one of the coolest features of Microsoft Excel, and also one of the least understood. Lots of people are interested in it and have great cases for using it. But I get many people who tell me that they don't use it because they don't really understand how it works. Is it magic? YES! Well, sort of. OK, no. But still, it's one of the coolest features of Excel, and well worth understanding.
In a nutshell, VLOOKUP is a database feature. Now, mind you, I don't recommend using Excel as a database tool. There are better tools for that, but I realize that I'm on the wrong side of the tide, here. People are going to use Excel as a database, and its because of features like VLOOKUP that you'll get away with that. Given my surrender on this issue, let me see if I can shed a little light on this feature.
You've got data in Excel - for arguments sake, lets say you keep your order data in there. I feel obligated to say, once again, this is a bad idea! Your order data belongs in Microsoft Access or Microsoft SQL Server, or some other database tool. But let's say you're going to do this anyways.
You need to calculate sales tax on your orders. Since you sell to customers across the United States, you need to know the sales tax rate in each state. So actually, you've got two sets of data in your Excel sheet: your order data, and your sales tax table. You need to link the two together. For each order, you need to take the customer's state, look up that state in your sales tax table, and retrieve the correct rate for that state.
VLOOKUP is the connection between the two sets of data. You'll create a formula in your order data table that grabs the state field, looks it up in the sales tax table, and returns the appropriate rate, using the VLOOKUP feature. The concept here is a relationship between the two sets of data. VLOOKUP capitalizes on the relationship between the two sets of data, saving you the time and effort of looking up that sales tax rate.
I hope this brief discussion of VLOOKUP piqued your curiosity on the subject. There are lots of learning resources to move forward with this feature, including this one: VLOOKUP Virtual Microclass.
I'm always happy to hear from fellow Excel geeks, so drop me a line if I can answer any questions!
About the Author
Kimberlee Martin is a Communication Specialist at Made to Thrive Consulting with 30 years of business experience. After spending several years working in IT as a software developer, Kim discovered that her favorite part of the job was effectively communicating thoughts between the user and the software. That led her back to school to earn her second degree in communications, where she discovered an affinity for marketing . Her passion is helping small businesses navigate the murky waters of digital communications, including websites, Facebook, Twitter, and other social media.
Contact Kim at kmartin@ThriveDrive.vision
© 2020 Made to Thrive Consulting, LLC. All right reserved.