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?
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.
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.
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.
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:
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).
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 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.
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:
- How to use VLOOKUP
- VLOOKUP: What does the True/False statement do?
- VLOOKUP: How to calculate faster
- How to VLOOKUP to the left
- VLOOKUP: Change the column number automatically
- VLOOKUP with multiple criteria
- Using wildcards with VLOOKUP
- How to use VLOOKUP with columns and rows
- Automatically expand the VLOOKUP data range
- VLOOKUP: Lookup the nth item (without helper columns)
- VLOOKUP: List all the matching items
- Advanced VLOOKUP Cheat Sheet
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: