This post is part of a series on one of Excel’s best functions, VLOOKUP. Check out the entire series here.
Then, subscribe to my newsletter for the latest.
Vlookup is the duct tape of Excel
Go back to our analogy of your “toolbox” of Excel skills. Vlookup is like the duct tape of your toolbox — it join data from multiple sources for you.
Our example: We have two sets of data — sales and profits by store, and square footage by store. We would like to merge these two sets of data. We’ll do this with the vlookup function.
Your first step is to hit “=” into cell G2 and write “=vlookup”.
The formula probably looks like some mad scientist’s incantation. So let’s break down what it means. The vlookup has four components:
Lookup_value:
The value for which you want to find an answer.
Table_array:
The data source that contains your answer.
Col_Index_num:
The column number from your data source that contains your answer. For example, if your answer is in column C of the data source, your column number is 3.
Range_lookup:
This asks whether you want an absolute or approximate match in your answer. This book will always use “False,” or absolute answers.
Let’s give it a try in cell L2 — retrieving the sales for Store 75.
Lookup_value:
Cell K2. This is the cell you want an answer for — you want to know this store’s sales.
Table_array:
data from cell B1 to D96. (Note — I type in through D600 to overcompensate for how much data we need — that’s usually okay to do.)
Why not include column A? Because the first column of your source data has to contain the field we are looking up. If we were looking up the sales of a region, we would use column A. But because we want store, we start with column 2.
Column index:
Starting with the store column, sales is column 2.
Range_lookup:
Again, always “False.”
Great first start! You know the basics of the vlookup now. I would encourage you to quiz yourself on this basic formula every day. Try vlookups on different data sets you encounter.
Leave a Reply