In previous blogs, I’ve talked about Excel’s VLOOKUP function. In one, I covered the purpose of VLOOKUP, and in the other, I explained the difference between VLOOKUP and HLOOKUP.
Today, let’s take a look at the arguments of the VLOOKUP function. I’ve never been happy with the way Microsoft explains them in the Insert Function dialog box – I find their terminology confusing. In this blog, I’m going to try to clarify these arguments for you.
Let me start by showing you what the function’s arguments look like, both in linear format and in “Function Arguments” dialog box format. The arguments are the same either way; I’m showing them to you in both formats because everyone learns in different ways. Refer to whichever makes more sense to you. Here’s the linear view:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
And on the Insert Function dialog box:
As you can see, there are four arguments of VLOOKUP. We’ll go over each of them, but first, let’s set up a case study, which I’ll refer to in further discussions.
As you can see in this example, I’ve got a worksheet where I’ll enter sales order data. From that data, I’ll use the State to look up the Sales Tax Rate in the second worksheet. On the first worksheet, Column E (the Sales Tax Rate column) is where we'll use the VLOOKUP formula. Let's look at each of the four arguments of the function:
Lookup_value
This is your known value; the value that you will use to look up some other data. In our case study, the Lookup_value is the state, which is in column C on the order worksheet. We’ll be looking that value up in the Lookup table (which is the list of states and tax rates). So our first argument will be C2 for the first row of data, and C3 for the second row.
Table_array
This is the table where you’ll find the value you want to retrieve. In our case study, it’s the entire table in the second worksheet – columns A (State) and B (Sales Tax Rate). The Table_array will be a reference to all columns and rows that contain our lookup data, so A1:B52 (50 rows for the states, 1 row for the heading, and 1 row for the District of Columbia).
Col_index_num
This is a number that indicates which column of the Table_array should be returned. In other words, what column contains the answer to your question. In our case study, the question is: “what’s the sales tax rate for XX state”. The answer columns from the second column of our lookup table, which is the Sales Tax Rate column. So we'll use "2" for this argument. To clarify, we're talking about a column in our lookup table here, so the second worksheet you see in the case study shown above. Also not that it's not "2" because the value we want is in column B. Rather, it's because the value we want is in the second column. If our states were in column Y and our rates were in column Z, we'd still use "2" for this argument, because in that case, column Z would be our second column.
Range_lookup
This argument is the hardest of the four to understand. It’s asking you if the values in your lookup table represent a range. This might be best explained by remembering back to our school days. To get an A on your test, you had to score between 93 and 100. So the range for an A is 93 to 100. The range for a A- was 90 to 92. The range for B+ was 87 to 89. And so on. If we were looking up test scores, we’d need to use a range lookup.
The range lookup feature can be a little complex, so I’m not going to try to cover it in any more detail for now. Instead, I’ll over-simplify it by saying that if you want an exact match, you say “False” for range lookup. If you want a close enough lookup, you say “True” for range lookup. In our example, we need to find an exact match on the state, so we'll use False.
The Final Result
So, in our case study, what would the formula look like in column E? Here it is in linear format:
And here's what it looks like on the Function Arguments dialog box:
I’m about to publish a self-paced course on VLOOKUP, which goes into much more detail about this function, including an in-depth discussion of the Range_lookup argument. Would you like to receive a free copy? I’m looking for beta testers, who will try out the course and offer feedback. I expect it’ll take about 1 ½ to 2 hours to complete the course. If you’re interested, fill out the contact form, and enter “VLOOKUP Course Beta Tester” in the comments. I'll hit you back with the details.
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.