All About VLOOKUP() in Excel

 Sep 14, 2015

Vlookup is a handy function in Excel. It is a quick and easy answer to lookup requests. It does exactly what we humans normally do when we look up something in a table. We first eyeball down the first column of the table to find the Key-value. Once we find the row we then scan across to find the relevant information for that value.

Let’s have an example:

vlookup in Excel

Here we’ve got a simple table.

We would like to find the Name of an employee with EmpNo of 12. This is the formula;

=VLOOKUP(12,$A$2:$D$6,2,False)

Let’s see what each element means;

  • The first argument of course is the Key-value, i.e. what we want to eyeball down the first column.
  • The second argument specifies the table we are interested in. There are a few remarks about the table that you select;
    • You should never include the table headings, i.e. row 1 above.
    • You should always fix the table by including the $ signs, as shown in the formula.
    • You should always specify a table whose first column contains the Key-value. For example if we were to find the Email address for employee Mary then you would’ve written this, =VLOOKUP(“Mary”,$B$2:$D$6,2,False). Notice the specified table $B$2:$D$6. It does not include column EmpNo because this column does not contain employee name Mary.
  • The third argument is the field number we would like Excel to return. In our case it is Name, which is the 2nd field in our table $A$2:$D$, so we type in 2. You should not use words “B” or “Email” for this argument, only a number. If we take the second example above, i.e. finding Email address by having the Name, since our table is now $B$2:$D$6, again the field number will be 2 because Email is the second field in that table.
  • The fourth argument should either be the word True or False. The default is True so if you don’t provide this argument, VLOOKUP assumes True. So what do True of False mean? Well the quick answer is True indicates Approximate match whereas False means Exact Let’s first talk about False.

When you type False what you mean is that if VLOOKUP can’t find a match for the Key-value in the first column of the table then it better be honest and tell you that, by returning #N/A (Not Available). This is what people most want from VLOOKUP.

When you type True however what you mean is that, if VLOOKUP can’t find a match for the Key-value in the first column of the table then it should process the largest value smaller than the requested Key-value. So here’s an example;

=VLOOKUP(10,$A$2:$D$6,2,True)

In this formula we are requesting Name for EmpNo 10, but employee number 10 doesn’t exist in the table but since we said True (Approximate match) it will return Mary, because EmpNo 8 is the largest value smaller than 10 in the above table. So at this point you might well this is a stupid feature. Why would I want a different employee number when I’m asking for employee number 10, and you are absolutely right. In this situation you should never use Approximate match but there are occasions when you should use True. Here’s an example:

Consider this table:

vlookup in Excel

Let’s say I have a student’s score of 76 and I want to grade it. I can look up this table to find the grade. So I type

=VLOOKUP(76, $A$2:$B$6,2,True)

Excel searches the first column and of course cannot find 76 so it looks at 70 which is the largest number smaller than 76 in this table and so will return “D”.

So in general you use True if you need to return a value that is constant within a range such as Tax tariffs, as shown in this table.

vlookup in Excel

An important note that you should remember is that True (approximate match) only works when the data on the first column is sorted ascendingly.

As another scenario imagine you have a worksheet that is working out a loan amortization such as in table below;

vlookup in Excel

Here we have written a balance formula in A8 and has Autofilled it down to an unknown row. We don’t know when the balance becomes zero. Let’s assume this is a big loan and will take a while to finish the payment. In B4 we need to write a formula to look up the table and find the Date when the Balance is zero, but of course the balance may never become exactly zero because it may switch straight to a negative number from a positive one, however the date at which the switch occurs is good enough for us. If we type;

=VLOOKUP(0,$A$7:$B$16,2,False)

We’ll get #N/A because 0 is not available in that table, but if we type;

=VLOOKUP(0,$A$7:$B$16,2,True)

Then it will return 17/02/2021 because 120.86 is the best match.

For more information, have a look at our Excel Training Courses.

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top