Gideon’s Excel Tips no. 1: Why was I told to always type FALSE at the end of a VLOOKUP?

When I am teaching Excel and introduce lookups to a new group of students, I almost always get someone who says that they were told to always enter FALSE as the last argument of a VLOOKUP function, but they have no idea why. This little tip will explain.

Excel’s VLOOKUP function works in two different modes, “Approximate Match” and “Exact Match”.

Approximate Match mode

To illustrate the first mode, imagine that we have a table giving the value of a car as it gets older. We can make a simple formula in cell D4 to look up the value from the table if we type the age of the car into cell D3.

In the formula, VLOOKUP(D3, A4:B10, 2)

• the first argument, D3 is the cell that contains the age of the car that we typed in,
• the second argument A4:B10 shows where the table of data is and
• the third argument, 2, is the number of the column containing the Residual values that we want the formula to find for us.

The animated GIF below shows what happens when we enter different ages into D3.

0, 1, 2, 4 and 10 years all work fine. But what happens if we enter a number of years that isn’t in the table? If we enter 3, 7 or 20 years into cell D3, the VLOOKUP formula still returns a value, which seems to be from the row immediately before where that value would have been in the table. For 3 years it gives us the value for 2 years; for 7 years it gives us the value for 6 years, etc..

Used this way, the formula makes sense. If we type in an age for the car that isn’t in the table, rather than give up with some kind of error message, it tries to be helpful by giving us a value that should be close to the value we are looking for. Approximate Match mode is a good way to use VLOOKUP when the lookup value (the age of the car in this case) is a number sorted in ascending order and when intermediate values make sense.

This is the default: if we leave out the optional fourth argument in the brackets VLOOKUP uses Approximate Match mode. Also note that the first column of the table that contains the lookup values MUST be sorted ascending: you’ll get garbage if it isn’t.

Exact Match mode

To illustrate Exact Match mode, imagine that we have an internal phone book containing the names and phone numbers of employees. We’ve set it up in the same way as the car prices example: you enter someone’s name into cell D3 and the VLOOKUP formula in D4 finds their phone number in the table.

In the animated GIF below we test it with various names and it works fine as long as the names are in the table. But when we try “Bell, Jill” the VLOOKUP function returns an #N/A error because there is no Jill Bell in the list! In this case, this is the appropriate way for the function to behave. It is telling us that the value is Not Available. We don’t want it to tell us the phone number of someone else just because their name is similar, do we?!

Exact Match mode is appropriate when the lookup values in the table are pieces of text or unique IDs of some kind, like product codes, ISBN numbers or of course people’s names, and it doesn’t make sense for the VLOOKUP formula to return a value that is close in the table when it can't find the lookup value.

To make VLOOKUP work in Exact Match mode you must add the fourth “range_lookup” argument in the brackets and set this to either FALSE or 0 (zero). In practice, many people use zero rather than FALSE because a zero is used in the MATCH function for a similar purpose.

Summary

Use VLOOKUP in Approximate Match mode

1. when your lookup values are numbers sorted in ascending order and
2. when it makes sense for VLOOKUP to return nearby values when the lookup value is not in the table.

Use VLOOKUP in Exact Match mode

1. when the lookup values are text strings or unique IDs and
2. you want it to return an #N/A error when it can’t find the lookup value in the table.

To use VLOOKUP in Exact Match mode you must set the fourth argument in the brackets to FALSE or zero, but the lookup values don’t need to be sorted.

Launching the Excel Masterclasses…

My company Office Productivity Training is launching a range of Masterclasses to fill the demand for Excel courses that are at the top end of the skill range. The courses are intended for Management Consultants and other analysts and managers who need to become true Excel power users.

The full range of courses is outlined here. The first course to be launched is a two-day course on the fundamental subject of Formulae. For more information or to book take a look here.

The courses will be run using webcasting technology in real-time and with small groups, so there will be plenty of interaction and lots of practical exercises. For the first course we will be using UK timings, starting at 9AM UK local time (but you are welcome to join from outside the UK) and the first course is scheduled for 13-14 June 2019.