VLOOKUP is one of the most powerful tools in Excel. It lets you look up an unknown value in a table when you know a corresponding value. For example, you can use a product number to find the corresponding price.
It sounds like a very simple thing, and it is, but it’s also easy to get wrong. And when you use VLOOKUP incorrectly, you’ll get the wrong data.
Let’s walk through the process to make sure you get it right.
The syntax of the VLOOKUP formula looks like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
That’s a lot of information, so let’s break it down.
The lookup_value is the value that you already know. So, in our example, it’s the product number. The table_array is a selection of cells that contain both the lookup value and the value that you don’t know (but want to look up). col_index_num tells Excel where to look for the value to return. We’ll talk about range_lookup a bit later.
One important thing to remember when using VLOOKUP: your data has to be organized in columns. This is almost always the case with spreadsheet data, but you might sometimes have data in rows. If you need to look information up in rows, you’ll need HLOOKUP.
Here’s an example of how we might use VLOOKUP. Let’s say we have product numbers, product names, prices, and stock status in a table:
Let’s use a known product number to look up an unknown price.
Here’s the information we’ll use:
This tells VLOOKUP to find the value “1000130” somewhere between A2 and D15, then to look in the third column from the left and return the value.
Here’s what the formula looks like:
=VLOOKUP(1000130, A2:D15, 3)
Executing that formula returns 9.99, the price of item 1000130.
You can easily change the arguments to get different information. Changing the product number is the most obvious choice; using 1000135, for example, will get you a different price. But you could also change col_index_num to 2 to have Excel return the product name instead of the price. Or 4 to return the stock status. You could also use a product name to look up price or stock status, like so:
=VLOOKUP(“red bottle cage”, A2:D15, 3)
Here’s where you might get a bit tripped up. In the formula above, we’ve left the table_array value the same as before. But now we’re looking up a value in a different column. Will that mess up the equation? No. Excel looks at the entire table for your value, then returns the value from the third column—no matter which column the lookup value is in (as long as it’s to the right of the lookup value).
However, if you look for a product name instead of a product number, you might change the table_array to B2:D15. In this case, you’d need to use col_index_num 2 instead of 3, because the price is now in the second column of the array, instead of the third.
This is the kind of mistake that it’s easy to make with VLOOKUP. Whenever you’re using the function, make sure to double check your table array and column index numbers to make sure it’s returning the right information.
One way that you can minimize the chances of this happening is to write a formula that will always return the right information and make it easy to put in new lookup values.
In our case, we might do something like this:
Changing the lookup value to a cell reference means whatever you type in that cell will become the lookup value in the VLOOKUP. With the setup above, all you need to do is type in a product number and you’ll get the price back.
If you decide to use this method, you’ll want to make sure that you don’t change the VLOOKUP formula. Or that if you do, you also change the corresponding text to make sure you always know what the formula is returning.
And if you change the structure of the table, you’ll have to update the formula to match.
We still haven’t talked about the final, optional argument: range_lookup. This is a TRUE or FALSE argument that tells Excel whether you want an exact match or a “close” match.
If you set this argument to FALSE (or 0), Excel will only look for an exact match to your lookup value. If it’s set to TRUE (or 1), Excel will use some rounding and approximation to find the value closest to the one you entered.
In general, you’ll want to use exact matches. Approximate matches are useful in a small number of edge cases.
With all of these things in mind, you can use VLOOKUP to find an unknown piece of information when you know something about what you’re looking for. It’s an intimidating function—especially with all of the extensive guides to VLOOKUP out there—but when you break it down to the basics, it’s actually quite simple.
It’s when you start using it on more complicated data or combining it with other functions that you’ll see the true power of VLOOKUP.
Discover more exercises with answers about Access and Excel