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.
| Argument | Description |
|---|---|
| lookup_value | The value you wish to ‘lookup’ in the first column of the table_array. |
| table_array | Table 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_num | The 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.
- 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)
So we can tie all that information together into the formula below, easy right?
Quiz # 1 – Exact Match
Now that you’re a VLOOKUP professional, can you correctly identify the formula needed 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).
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
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.
