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 the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2020 North Port Solutions, LLC. All right reserved.