Excel Function of the Month – VLOOKUP


The Excel Function of the month is

VLOOKUP

Description

The VLOOKUP function is very useful for looking up a value in the leftmost column of a table array and then returning a corresponding value in the same row in a column you specify.

Usage

Here is our table. You can see we have 5 rows with data and 3 columns.

Let’s say we want to look up someone’s first name and automatically see what position they held. The VLOOKUP function can do this for us. I’m going to create the lookup in another sheet.

Here is what I created in my second worksheet (Sheet2):

What I want to happen is, when I type (in A4) in the first name of someone from the table in the List worksheet, I want their position to automatically be filled in B4. So, in other words, this:

Here is the function. I will show you first, then discuss it.

A typical VLOOKUP function is broken down into 4 steps:

  1. The value being looked up/for
  2. The table array where the value and corresponding value exist
  3. The column where the corresponding value exists (referred to as column index)
  4. Whether one is looking up a range of values or a specific answer. i.e. range lookup is either TRUE or FALSE.

So, let’s look at my example:

1. The value being looked up/for

I start with what I’m looking for. In this case, whatever I typed into A4.

2. The table array where the value and corresponding value exist

Next, I tell Excel where the table array is i.e. where the value and it’s corresponding value are located. In this case, the table array is located on the ‘List’ worksheet. Below you can see I selected A2:C6.

From the above selection, make note that the first column contains the lookup value (what you are looking up) and then note which column the corresponding value is located in. You can see that the position information is located in the third column of our array.

Below you can see the second criteria in the VLOOKUP function, the table array. The word ‘List!’ is there because that is the worksheet that the data is contained in.

3. The column where the corresponding value exists (referred to as column index)

Now comes the important bit, telling the VLOOKUP function which column to look in for the corresponding value for our initial lookup value. In other words, “look for ‘William’ in the first column of my array and tell me what value is in the third column of my array. This is also known as the Column Index number.

So, at this point we’re telling the VLOOKUP to look a specific value (cell A4) in the table located on the List worksheet (Celll A2:C6). It will look in the first column for the lookup value and now we’ve told it to find the corresponding value in the third column of our array (column index number 3).

Just to recap:
=VLOOKUP(A4,List!A2:C6,3,FALSE)

4. Whether one is looking up a range of values or a specific answer

The very last criteria is TRUE or FALSE. When we are looking for a specific value or answer, like in the example above, then we are NOT looking for a value within a range. So, the range lookup is FALSE. For now, I’ll leave it at that and cover range lookup in another post instead! đŸ™‚

Check out my Excel posters for your classroom

Similar Posts: