VLOOKUP DECODED

Vlookup is one of the most commonly used functions of excel. There are numerous data lookup tasks that we want to do and vlookup does them just as a charm. Before getting into the syntax of the function, let us first understand what it really does.

We use the vlookup function in our head all the time. It may be while shopping for our favorite products on amazon, reading the table of contents of a book to find the page number, reading a train status on the railway station or while looking for the price of a tea at our favorite tea shop. Below is a snippet of the beverage menu of my favorite tea shop, Chai Point. How would you find the price of the Classic Chai which serves 3-4 people?

If we had to list down the steps involved, it would be as follows:

  1. Scroll down the Menu to find the Classic Chai
  2. Scroll to the right to the column where price for the required size is noted
  3. Read the price!

Well! This is exactly what Vlookup in excel does!

A few points to note though:

  1. The above menu data is organized in a vertical format, therefore, Vlookup (read Vertical lookup)
  2. Classic Chai appears only once in the above menu
  3. If the Classic Chai appeared twice, your mind (or rather excel Vlookup) would have stopped on the first instance it saw Classic Chai
  4. The product name “Classic Chai” appeared in the left most column of the menu.

Now that we understand what Vlookup actually does, lets now see how it is implemented in excel.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • Lookup_value: The reference to value that you are looking for. In the example above, we are finding out the price of Classic Chai. Hence, “Classic Chai” is the lookup value.
  • Table_array: The array of data having the value that you are looking. For the above example, the whole menu is our table array. Refer below snippet having the menu in excel format. Region A2:D11 should be the table array for this example.

Point to note: The column having the lookup value should be the leftmost column of the table array

  • Col_index_num: Column number of the value in the table array. This field tells excel which value to pull once the lookup value is found. Once excel finds “Classic Chai” in the first column of the table array, it should pull the value in the third column of the array to get the price of Classic Chai of size “Serves (3-4)”.

Point to note: The number of columns should be counted including the first column having the lookup value

  • Range_lookup: Exact match (0 or False) or Approximate match (1 or True). In this field, you have to tell excel that it should exactly match the lookup value when looking for the lookup value. This field is useful while looking up a sorted list when the lookup value is numerical and a range of value should give the same result. Will discuss this more in a separate post.

In simple plain English:

VLOOKUP(value you are looking up,

table region in which the value is available,

the column from which result should be pulled,

having an exact match)

With the above understanding, let us build the formula to pull the price of “Serves (3-4)” size Classic Chai.

Vlookup(“Classic”, A2:D11, 3, False)

And you have the desired price!!!

In the above example, try replacing the hard-coded menu item “Classic” with a cell reference where you have the criteria listed. In the snippet below, the lookup value is placed in cell “H4”. Therefore, replace the criteria “Classic” with the cell reference H4.

Notice, in the following snippet, I have used “0” instead of “False” for the range_lookup argument.

I hope this helps you to understand how Vlookup works. Follow the other posts to get into more detail of the tricks and limitations with Vlookup.

Hope you had a good tea while learning!

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

<span>%d</span> bloggers like this: