VLOOKUP: What does True/False do? (How to avoid errors!)

If you have ever looked up a value from another worksheet, I’m sure you will have used the VLOOKUP function. There are other lookup functions available, but by far the most popular is still VLOOKUP. My guess is when you create this formula, you enter False or 0 as the last argument. But what does the range_lookup argument do? Does it matter if we use True or False (or 1 or 0)?

That’s what we are considering in this post: In a VLOOKUP should you use true or false?

Table of Contents

VLOOKUP Range_Lookup argument

The 4th argument of VLOOKUP is the range_lookup. Technically, it is an optional argument; therefore, we do not need to enter True or False; we could leave it blank. However, do not be fooled. This argument is critical to VLOOKUP calculating the correct result.

If we do not enter a value for the range_lookup, VLOOKUP defaults to using True. In many circumstances, this could lead to significant errors without us even realizing it. Therefore, I recommend you don’t ever leave this argument blank. Instead, always decide whether it should be True or False.

Excel VLOOKUP formula window

In the function library, the range_lookup is described as:

A logical value; to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.”

What does all this mean? Let’s find out.

VLOOKUP False

We will look at False first because it is easier to understand. When using “False” or “0”, the function returns an exact match. Effectively, Excel starts at the top of the list and works down item by item. If the lookup value exists in the list, it returns a value; if it does not, it returns #N/A. 

This is easy to understand; if the lookup_value is there, it returns a value; otherwise, it returns the #N/A. Simple.

VLOOKUP True

However, what happens if we use True or 1 as the 4th argument? We can receive all sorts of strange results. If we do not know what VLOOKUP is doing, it can result in significant errors.

Using True should provide the “closest match”. But you may be surprised by the definition of “closest match”. In VLOOKUP, it is defined as the next largest value that is less than the value being looked up.

Let’s look at an example:

VLOOKUP example - data sorted
FormulaResult
 =VLOOKUP(20,A2:B9,2,TRUE)Bill
 =VLOOKUP(35,A2:B9,2,TRUE)John
 =VLOOKUP(61,A2:B9,2,TRUE)Kevin

20 is an exact match, so Bill is returned. For the values of 35 and 61, the next largest values are 30 and 60, so the results for these values are returned (John and Kevin respectively).

The warning

Now time for the warning. If you use True when your list is not sorted, it may give you the wrong result. Even if there is an exact match, it can still give you the wrong result. Let’s change our example to a non-sorted list and see what happens:

VLOOKUP example - range unsorted
=VLOOKUP(20,A2:B9,2,TRUE)Bill
=VLOOKUP(35,A2:B9,2,TRUE)Bill
=VLOOKUP(50,A2:B9,2,TRUE)Bill
=VLOOKUP(90,A2:B9,2,TRUE)John

VLOOKUP True performs a Binary Search. It does not start at the top and work down. Instead, it starts in the middle and eliminates 1/2 the values. Then moves into the remaining data, and goes to the middle, and eliminates 1/2 the data again. This continues until there is only a single value remaining.

This means that from a data set of 1,000,000 items it can return any value in 20 calculations. Which makes VLOOKUP with True very fast. However, it is the nature of a binary search that causes the issues.

If we are looking for 35:

  • VLOOKUP with True starts in the middle of the table at 70 (row 5 in the example). 35 is less than 70; therefore, VLOOKUP goes upward, and rows 6 thru 9 are eliminated.
  • It moves to the middle of the top half to 20 (row 3). 35 is more than 20, so rows 1 & 2 are eliminated, and VLOOKUP looks downward at the remaining rows. 60 is the only value left to look at.
  • 35 is less than 60. So, it goes up one row to 20 for the result, which is Bill.

If we are looking for 50 (which with False would provide an exact match), it does not return the value we would expect:

  • VLOOKUP with True starts in the middle at 70. 50 is less than 70; therefore, VLOOKUP goes upward, and rows 6 thru 9 are eliminated.
  • It moves to the middle of the top half to 20. 50 is more than 20, so rows 1 & 2 are eliminated, and VLOOKUP looks downward at the remaining rows. 60 is the only value left to look at.
  • 50 is less than 60. So, it goes up one row to 20 for the result, which is Bill.

If we are looking for 90:

  • VLOOKUP with True starts in the middle at 70. 90 is greater than 70, so VLOOKUP moves downwards, and rows 2-5 are eliminated
  • It moves to the middle of the bottom half to 50. 50 is less than 90, so VLOOKUP moves downwards again, and rows 6-7 are eliminated.
  • It moves down to 80. 90 is greater than 80, so that row is also eliminated.
  • It moves down to 30 and uses that as a result because all other options have been eliminated.

TOP TIP: If you cannot guarantee the data you are using will always be sorted, DO NOT use True. If a row could be added to the bottom of a list, which is out of order, DO NOT use True.

Other lookup formulas

Many other lookup formulas exist with similar traits:

HLOOKUP operates the same as VLOOKUP, but rather than working down a list; it works across from left to right.

INDEX/MATCH has similar properties but uses numbers rather than True or False and has 3 possible values:

  • 1 : Finds the largest value which is less than or equal to (the same as True for a VLOOKUP) – This is the default.
  • 0 : Finds an exact match (the same as False for a VLOOKUP)
  • -1 : Finds the smallest value which is greater than or equal to. The data must be sorted in descending order.

XLOOKUP is the most powerful lookup function and provides the most flexibility. It has a match_mode argument which can be

  • 0 : Finds an exact match – this is the default value.
  • -1 : Finds the smallest value which is greater than or equal to
  • 1 : Finds the largest value which is less than or equal to
  • 2 : Wildcard character match

These arguments can be combined with the search_mode argument to specify whether:

  • 1 : Search from top to bottom – this is the default.
  • -1 : Search from bottom to top
  • 2 : binary search from top to bottom. Just like VLOOKUP with True, the lookup_array must be in sorted ascending order to return the correct value.
  • -2 : binary search from bottom to top. The lookup_array must be in sorted descending order to return the correct value.

What may not be clear from this list of arguments is that if using 1 or -1 as the search_mode, XLOOKUP does not need to be sorted to find a value that is the next smallest or next largest. XLOOKUP has the added advantage of searching from the top or the bottom.

Only if the search_mode is 2 or -2 could XLOOKUP return similar incorrect values as VLOOKUP. Therefore it is probably time to abandon VLOOKUP in favor of XLOOKUP.

XMATCH has similar search criteria to XLOOKUP, but returns a position rather than a corresponding value.

Conclusion

Using True vs False in VLOOKUP can have a huge impact on the result. Most of the time we probably want an exact match on unsorted data. However, this is not the default. As a result, VLOOKUP is susceptible to creating errors for anybody who doesn’t understand what the arguments do.

Other posts about mastering VLOOKUP:


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment