#### 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:

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:

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.

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;

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 NowNext up:

- New security features in Windows 10
- Understanding your customers with 6 questions
- Put that marker down and Redact in Acrobat please!
- What’s a JavaScript Closure?
- Use a slicer to make better business decisions
- The best features in Office 2016!
- “If you can’t measure it…”
- No need for meeting notes anymore!
- Setting up your models in MVC
- Create a Chart Template in Excel

Previously

- Creating a layout for Word and Excel
- Implementing paging easily
- Leadership, Management and Feedback
- Do the Quick Step in Outlook
- OneDrive for Business Gets Some Major Enterprise Updates
- Rounding Numbers in Excel, Part 2
- Word Equations
- Dinosaur or Mega-Soar
- Using the WebView Control in Windows Apps
- Creating Labels in Microsoft Word