What does it do?

VLOOKUP can search a table of data (first column only) for a variable and, if a match is found, return a piece of data from that matching row.

The formula

This function has 4 arguments, the last one being optional as indicated by the [square brackets]. However, even though the last argument is optional you’ll probably still always want to set it. We’ll cover this in more detail below.

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
ArgumentDescription
lookup_valueThe value you wish to ‘lookup’ in the first column of the table_array.
table_arrayTable range for your data. The range must include the column you wish to search the lookup_value for & the column you wish to return data from.
col_index_numThe column number that contains the data you seek. The first column of the table_array = 1, and increases sequentially as you move right.
range_lookup[Optional] Default = True if not set by user.
True or 1 : Search for the closest match
False or 0 : Search for exact match

Example #1 – Exact Match

Okay so that’s the technical stuff out of the way, let’s put that knowledge to good use and VLOOKUP the price of some items in my store! Let’s take a look at the Parts List shown in the image below, and we’ll build the formula to find the PRICE of item WTF003.

VLOOKUP Exact Match
  • What piece of data do we want to lookup? The Part ID in Cell B8
  • Which column will we vertically search for the Part ID? Column A
  • Which Column has the data we want to return? Column C (3rd column)
  • Do we want an exact or nearest match? We want to find the exact Part ID (remember we use FALSE to specify this)
VLOOKUP Arguments

So we can tie all that information together into the formula below, easy right?

=VLOOKUP(B8,A1:D6,3,FALSE)

Quiz # 1 – Exact Match

Now that you’re a VLOOKUP professional, can you correctly identify the formula needed here?

VLOOKUP (by Part Description) the remaining ‘QTY ‘of the ‘Invisibility Cloak’?
=VLOOKUP(“Invisibility Cloak”,A1:D6,4,FALSE)
Incorrect: Remember our lookup_value is the Part Description (column B)
=VLOOKUP(“Invisibility Cloak”,B1:D6,4,FALSE)
Correct!
=VLOOKUP(“InvisibilityCloak”,B1:D6,4,FALSE)
Incorrect: We asked for an EXACT match. Our lookup_value seems to be missing a space here

Example #2 – Approximate Match

You’re probably wondering why would you ever want set the optional range_lookup argument to TRUE, which is also known as an ‘approximate’ match. For example, consider the spreadsheet below, which will calculate the discount % applied to a sale based upon the quantity purchased (higher QTY = higher discount).

VLOOKUP Approximate Match

As you can see, we have entered a QTY Sold of 39, which doesn’t exist in Column A of our table. So what the approximate match does, is find the closest match that is smaller than 39. Even though we would have probably expected the “nearest” match to be 40. In this use case, the discount applied is 30%.

Quiz # 2 – Approximate Match

In the example above, what would the discount be if the user entered 125 into cell B8?
60%
Incorrect: This value does not exist in Column B, so it cannot be returned via VLOOKUP
50%
Correct!

As you can see, this feature of VLOOKUP can be quite useful. If we tried doing this using the exact match (FALSE) argument, our lookup table would need to have a % discount for each potential QTY amount (1, 2, 3, 4 to infinity). This is unnecessarily tedious!

Error Messages

It’s important to note, that your table_range might not always contain the value you are attempting to lookup. So instead of returning a value, the VLOOKUP formula will return an error message. If you want to learn more about these, check out the article below.