A look at the VLOOKUP function


Since Bill Jelen (MrExcel) launched a VLOOKUP week, I figured it was a good occasion to take a look at this function and compare it with the INDEX/MATCH alternative. In order to prevent my post from being too long, I decided to dedicate this entry to the VLOOKUP function only.

Basics

What the function does

What is the VLOOKUP function about? It allows you to search for a key in a table and find the corresponding value in another column on the corresponding row.

Suppose you have the following reference table for product prices and you want to look for the price of product XT987.

image

First, you would look for the value XT987 in the first column and find the row where this value is.

image

Then, you would move to the second column (the price column) to retrieve the desired value.

image

The syntax

This is what the VLOOKUP function does for you. The function has two syntax forms and comes in two flavors.

=VLOOKUP(key_value, lookup_table ,result_column_index, approximate_search)
=VLOOKUP(key_value, lookup_table ,result_column_index)

key_value is actually the value we are looking for. In my example, this would be XT987.

lookup_table would be our reference table.

result_column_index would be the index our price column (that is, 2).

approximate_search is an option specifying how Excel will look for the value. This parameter tells Excel to perform an approximate search or not. It takes the values FALSE or TRUE and is optional (see the second syntax form), the default value being TRUE. Note that the option TRUE also requires you to sort your table in ascending order (of the key value) for the function to work properly.

Usually FALSE is the option you want to use. Let us see what the difference is.

Approximate or exact search

The case for an exact search

Suppose you have a product number Y5678 and want to find the corresponding price in your table. Now, as you may have noticed, this product is not in our table. Depending on the value you chose for the last parameter, you will get different results.

For an exact search, the formula will look like this:

=VLOOKUP(“Y5678”, lookup_table ,2, FALSE)

and Excel will return #NA since it could not find any corresponding value.

For the  approximate search, the formula will look like this:

=VLOOKUP(“Y5678”, lookup_table ,2, TRUE)

or like this:

=VLOOKUP(“Y5678”, lookup_table ,2)

In that case, Excel will go through the product numbers and will look for Y5678 or the last value that is “smaller” than Y5678. In our case, XT987 is the last value in our Product Number column that comes before Y5678 in alphanumeric order.

Excel will thus return 569, which may not be the result you expected.

The case for an approximate search

Interval searches

Approximate search is well suited for interval searches.

Suppose you have the following table.

image

You want to associate the corresponding shipment fee with each product.

Suppose the shipping price of a product is based on its weight.

If a product weights less than 5 kg shipment will cost 5€.
If a product weights no less than 5 kg but less than 20 kg shipment will cost 20€.
If a product weights no less than 20 kg but less than 90 kg shipment will cost 45€.
If a product weights more than 90 kg shipment is not available.

Then you could use the following lookup table to map the min weight to the shipment fee:

image

Then the following equivalent formulas will give you expected results

=VLOOKUP(weight, lookup_table_shipment, 2)
=VLOOKUP(weight, lookup_table_shipment, 2, TRUE)

When applied to the table, this will give the following results.

image

Let us take the product ZLM988 as an example. The weight of this product is 86 kg. Excel will look for the last key (the minimum weight) in the lookup table, that is equal to or smaller than

86. The corresponding entry is 20. Excel will then return the shipment on the corresponding row and will thus correctly return 45.

Partial searches

This problem comes from a discussion group on a well-known professional networking platform. The original poster was looking for a way to categorize products based on their product number. Now this series number would start with letters just as in my previous examples. Now the part number could have between 1 and 3 characters (I guess, maybe more in the future) and the list of part numbers would be updated regularly. People provided some smart sophisticated solutions, but none had come up with most simple one. Which is to use the VLOOKUP function.

Now, let us take our last table, and categorize these products based on their part number.

image

For this, we will use the following lookup table.

image

Now, one of the following formulas will do the job:

=VLOOKUP(product_number, lookup_table_categories, 2)
=VLOOKUP(product_number, lookup_table_categories, 2, TRUE)

and will return the expected results:

image

Be cautious though. Since we are using an approximate search, we may still have the issue that some part number is missing in the lookup table, thus leading to an incorrect result. Suppose you add another product XSM654 to the data table, whose part number XSM corresponds to the Electronic Devices category. If this part number is not available in our lookup table, the last entry whose value precedes XSM654 in alphanumerical order, is XT. Excel will legitimately return an incorrect category (Books).

You will thus have to implement some formula, to catch these sort of errors.

Note though that you can also use VLOOKUP to retrieve the part number from the lookup table. In our last example, the formula
=VLOOKUP(product_number, lookup_table_categories, 1)
will return XT, when the product_number equals XSM654.

This allows to test whether our lookup table has been correctly maintained or not. For example, you could use the following formula to test whether or not product_number actually starts with the part number returned by the VLOOKUP function:
=SEARCH(VLOOKUP(product_number,lookup_table_categories,1),product_number) = 1

Advertisements

3 Responses to “A look at the VLOOKUP function”

  1. From ‘The Data Specialist’: “A look at the VLOOKUP function” « VLOOKUP WEEK Says:

    […] “Since Bill Jelen (MrExcel) launched a VLOOKUP week, I figured it was a good occasion to take a look at this function and also compare it with the INDEX/MATCH alternative. In order to prevent my post from being too long, I decided to dedicate this entry to the VLOOKUP function only.” … To Read More Click Here to go to The Data Specialist’s Lesson. […]

  2. A look at Index & Match functions « The Data Specialist Says:

    […] A look at the VLOOKUP function […]

  3. Equivalent of VLOOKUP in DAX–Part I « The Data Specialist Says:

    […] details on VLOOKUP here, there, or everywhere on the […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: