Why do we need FALSE in VLOOKUP?

In VLOOKUP, there is only one optional argument:[range_lookup].Using this r argument, we can provide two parameters, i.e., TRUE or FALSE.

As beginners, 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 provide TRUE or FALSE. So, let us understand 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.

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.read more.

Table 1 shows the “Company Name” and its “Revenue details.” However, in Table 2, we have only “Company Name,” so we need to find the “Revenue” details from Table 1 based on the “Company Name” available in Table 2.

So 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.

  • Open the VLOOKUP function in the F3 cell. Choose the Lookup Value as an E3 cell. Next, choose the VLOOKUP table array as the Table 1 range. Column Index Number as 2. The last argument is [Range Lookup]. 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 this is not 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 these values differ, we still got the revenue details as $120,161. It is the revenue detail of “Florida Incorporation.” Similarly, look at the F8 cell result. In this case, the company name is “Florida Inc,” but the actual company name is “Florida Incorporation,” so these two values are not exact. Still, 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, the 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 will end up. So, 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. We have only changed the [range_lookup] criteria from TRUE to FALSE and looked at the results. We have the error values for all those cells that do not have exact lookup values, so whichever cells have the exact lookup value in Table 1 has the perfect results.

In this cell, we have the word “Florida Milk,” but the actual company name in Table 1 is the “Florida Milk Federation.” Even though these values differ, we still got the revenue details as $120,161. It is the revenue detail of “Florida Incorporation.”

In this case, the company name is “Florida Inc,” but the actual company name is “Florida Incorporation,” so these two values are not exact. Still, because we have used the match type as TRUE, i.e., approximate match, it has returned the approximate match result.

In this case, the 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 will end up. So, we need to use FALSE as the [Range Lookup] matching criteria.

The same formula. We have only changed the [range_lookup] criteria from TRUE to FALSE and looked at the results. We have the error values for all those cells that do not have exact lookup values, so whichever cells have the exact lookup value in Table 1 has the perfect results.

Things to Remember

  • The need for using TRUE may not arise, so always stick to FALSE as the criteria for [Range Lookup]The [Range Lookup] is an optional argument. 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 provide 0 as the criteria.

This article has been a guide to VLOOKUP False. Here, we discuss the importance of the FALSE statement in the VLOOKUP function [Range Lookup], along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Errors in VLOOKUPVLOOKUP with Two CriteriaVLOOKUP TrueVLOOKUP Power BI