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 excel.
In Table 1 we have Company Name and Its Revenue details and in Table 2 we have only Company Name, so need to find the Revenue details from Table 1 based on Company Name available in the Table 2.
Open VLOOKUP function in F3 cell.
Choose the Lookup Value as E3 cell.
Next choose the VLOOKUP Table Array as Table 1 range.
Column Index Number as 2.
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 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 has the exact lookup value in Table 1 has got the perfect results.
So in 99.999% of the time, we need exact matching result so FALSE is the criteria we need to use to get exact matching results.
Things to Remember
- The need of 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 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 downloadable excel template. You may learn more about excel from the following articles –