Why we need FALSE in VLOOKUP?
In VLOOKUP, there is only one optional argument, and that argument is [Range Lookup]. Where this r this argument, we can provide two parameters, i.e., either TRUE or FALSE.
As a beginner, we may not have realized this because, at the learning stage, we are in a hurry, so this goes unnoticed.
Based on the kind of range lookup we give is important. As we have learned above, we can give TRUE or FALSE so let’s learn what these two arguments do.
TRUE or 1: If we provide TRUE, it will look for an approximate match.
FALSE or 0: If we provide FALSE, it will look for an exact match.
Since [Range Lookup] is an optional argument, it will take TRUE as the default parameter.
Ok, now look at the below data tables in excelData Tables In ExcelA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section..
In Table 1, we have Company Name and Its Revenue details, and in Table 2, we have only Company Name, so we need to find the Revenue details from Table 1 based on Company Name available in Table 2.
Follow the steps in to use false in excel vlookup.
- Open VLOOKUP function in the F3 cell.
- Choose the Lookup Value as an E3 cell.
- Next, choose the VLOOKUP Table Array as Table 1 range.
- Column Index Number as 2.
- The last Argument is [Range Lookup] and mention it as TRUE or 1 in the first attempt.
- For the naked eye looks like we have got revenue details for all the companies, but actually, this isn’t the matching data because of the cell E3.
In this cell, we have the word “Florida Milk”, but the actual company name in Table 1 is the “Florida Milk Federation”. Even though both these values are different, we still got the revenue details as 120,161. This is actually the revenue detail of “Florida Incorporation”.
- Similarly, look at the F8 cell result.
In this case company name is “Florida Inc”, but the actual company name is “Florida Incorporation”, so these two values are not exact, but because we have used the match type as TRUE, i.e., Approximate Match, it has returned the approximate match result.
- However, look at the cell F7 for the company “ABC Company”.
In this case lookup value is “ABC Company”, but in Table 1, we have “ABC Company Ltd” but still got the correct result. So using TRUE as the criteria for the [Range Lookup], we cannot exactly know how it is going to end up. So this is the reason we need to use FALSE as the [Range Lookup] matching criteria.
- For the same formula, change the [Range Lookup] criteria from TRUE to FALSE (0) and see the result.
The same formula the only thing we have changed is [Range Lookup] criteria from TRUE to FALSE and look at the results. For all those cells which are not exact lookup values, we have got the error values, so whichever cells have the exact lookup value in Table 1 has got the perfect results.
So in 99.999% of the time, we need the exact matching results, so FALSE is the criteria we need to use to get exact matching results.
Things to Remember
- The need for using TRUE may not arise, so always stick to FALSE as the criteria for [Range Lookup]
- [Range Lookup] is an optional argument, and if you ignore it, it will take TRUE as the default matching criteria.
- Instead of TRUE, we can give 1 as the criteria, and instead of FALSE, we can give 0 as the criteria.
This has been a guide to VLOOKUP False. Here we discuss the importance of FALSE statement in VLOOKUP function [Range Lookup] along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –