Excel VLOOKUP Function

//

Nico Mellett

In a Nutshell

VLOOKUP is a Microsoft Excel function that returns a value in a specified column of a range, based on a specified lookup value

What does that mean? It searches for the specified value in column 1 of your table, and returns a matching value from another column. Examples include looking up a product price based on a specified product code, or looking up a grade (A+, B, etc.) based on a specified test score.

It is categorised in the Lookup & Reference Function Library on the Formulas tab:

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function syntax has the following arguments (inputs):

  • Lookup_value – The value to look for in the lookup range (i.e. table_array). This value should be in column 1 of the lookup range (i.e. the left-most column)
  • Table_array – The table that contains the value that we want to look up. The left-most column contains the lookup value.
  • Col_index_num – Counting from the left, col_index_num is the number of the column containing the value we want to look up. E.g. if column 1 contains part numbers and column 2 contains part descriptions, if we want to look up the part description, we will use 2 as col_index_num
  • Range_lookup [optional] – If the lookup value can’t be found, the closest match is used as lookup value. By default, this is TRUE, so if we need an exact match we need to ensure the range_lookup value is FALSE)

What this means:

=VLOOKUP(What to look for (in col 1 of our table), Where to look (i.e. the table that has the value we’re looking up), In which column is the result we need, Is it a nearest/exact match)

Notes

  • The “V” in VLOOKUP stands for vertical. It searches for the lookup_value by vertically running down column 1 of the lookup table (the table_array).
  • Even though VLOOKUP is used for an exact match in most cases, if the range_lookup is left out the function will default to an approximate match. If we need an exact match, remember to add ,FALSE at the end.
  • The lookup_value must be in column 1, so VLOOKUP is generally constrained to looking up values to the right.
  • Use wildcards in the lookup_value
    when the full value isn’t known, e.g. use a lookup_value of “*Smith” to find the first item in column 1 that ends with “Smith”, or use a lookup_value of “E???” to find the first item in column 1 that is exactly four characters long and starts with “E”, e.g. Elmo, Erin or E001.

Examples

Let’s take a look at a few examples of VLOOKUP in action. The example file can be downloaded here.

Example 1 – Exact match

In this example we have a table (the table_array) with a list of spare parts and their prices. We want to use a VLOOKUP to find the price of one of these spare parts. We’ll provide a part number in cell D2 and the VLOOKUP will bring back the price, in cell D4.

In the image below, the part numbers are in column B of the lookup table and the prices are in column E.

The lookup formula in cell D4 is VLOOKUP(D2, B6:E14, D3, FALSE)

The VLOOKUP works as follows:

  1. THE GOAL: We’re looking for the price of the part specified at cell D2, being “LF5” (this is our lookup_value).
  2. The VLOOKUP scans down column 1 of the lookup table (table_array) until it finds the part number (LF5) in cell B11.
  3. It then looks across to the right and bring back the value in column col_index_num.

    In our formula, col_index_num points to cell D3 where it picks up the value 4. So it looks for the result in the 4th column of the table

  4. RESULT: The value found in column 4 is $13.

The last argument (range_lookup) is FALSE. It had no impact on the result of this specific lookup, but if LF5 was not found in the table, adding FALSE would make sure the formula brings back #N/A (a “not available” error) instead of incorrectly bringing back the price of a different item.

Example 2 – Approximate match

In this example the table_array represents a table of scores and grades. A score of 30 is a grade of “F”, a score of 80 is a grade of “A”, and so on.

It’s clear that an exact match lookup won’t work for this example as there are only a few scores listed in column 1. If we tried to look up a score that’s not in the list, like 64, or 45, or 86, it would run down the first column, find nothing and bring back an #N/A error. That’s where the approximate match comes to our rescue. We perform an approximate match by using a range_lookup of TRUE, or by omitting it completely as range_lookup then defaults to TRUE.

For an approximate match lookup to work, the lookup column needs to be sorted
in ascending order.

In the image below, we are looking for the grade represented by a score of 62. In the lookup table, the scores listed in column 1 are the bottom boundaries for the grades listed in column 2.

The lookup formula in cell D5 is VLOOKUP(D2, B9:C15, D3)

This VLOOKUP works as follows:

  1. THE GOAL: We’re looking for the grade represented by the score in cell D2, being 62 (this is our lookup_value).
  2. The VLOOKUP starts scanning down column 1 of the lookup table (table_array) and stops looking as soon as the value in column 1 is greater than the lookup_value. It then backtracks one spot and brings back the value in the result column.

    For this example, it sees the first item is 30, which is less than lookup_value of 62, then looks at the next item down, which is 40, then the next, and keeps going until it’s gone past the lookup_value. So as soon as it hits 70, it’s gone too far and steps up one row, back to 60 and looks across into column 2 for the result, being “C”.

  3. RESULT: The result is therefore the last value that was found before exceeding the lookup_value, which was “C”.

The last argument (range_lookup) is omitted, so it defaults to TRUE, to indicate an approximate match.

Example 3 – Approximate match with an unsorted table (a big no-no!)

This example uses the same information as Example 2 above (looking up a grade based on a score), but this time the table in unsorted. The scores and grades in the two columns still match up properly, but in column 1 we can see a score of 40, then 50, then 80, etc.

For this one we are again looking for the grade represented by a score of 62. The lookup formula in cell D5 is VLOOKUP(D2, B9:C15, D3)

Let’s see what can go wrong if the table is not sorted before performing an approximate match VLOOKUP:

  1. THE GOAL: We’re looking for the grade represented by the score in cell D2, being 62 (this is our lookup_value).
  2. As we’ve seen above, the VLOOKUP starts scanning down column 1 of the lookup table (table_array) and will stop looking as soon as the next item in column 1 is greater than the lookup_value. It will then backtrack one spot and bring back the value in the result column.

    The first item is 40, which is less than the lookup_value of 62. It then looks at the next item down, which is 50, then the next, which is 80. This is greater than lookup_value, so it’s gone too far, steps up one row, back to 50 and looks across into column 2 for the result, being “D”. This is wrong!

  3. RESULT: The last value that was found before exceeding the lookup_value, was “D”. The correct category is 60, but the VLOOKUP never got that far because the 80 was in the way so the 50 that came just before it was used instead.

Errors

#N/A – The #N/A (i.e. “Not Available”) error message indicates that the VLOOKUP function was unable to find the lookup_value in the table.

This could happen for various reasons:

  • Extra spaces in either the lookup_value or the values in the lookup column (column 1 of the lookup table). These spaces can be cleared out using the TRIM function.
  • The lookup_value is a number that is formatted as text, whereas the values in the lookup column are appropriately formatted as values (or vice versa). Change the numbers that are formatted as text back into numbers, by selecting all of the “text” numbers, clicking Text-to-Columns in the Data tab, and click Finish. They should now all be numbers and your VLOOKUP should work perfectly.
  • There is a typo in the lookup value
  • The lookup_value is not found in the lookup table. E.g. the lookup_value is EMPL0032 but the lookup table only lists employees up to EMPL0031. In this case, check whether the lookup_value is correct and also check whether the lookup table is up to date.

Excel Versions Supported

All versions

Leave a comment

Subscribe to our YouTube Channel for free tips and tutorials!

Connect