Updated May 8, 2023
VLOOKUP with True
When we apply the Vlookup function, it is obvious that we always look for the exact match. When we get the #N/A, we also assume that the value we are looking for is unavailable in the lookup table. But we have yet to try using Vlookup with a TRUE value match. We can use two criteria in Vlookup functions, which are TRUE and FALSE. FALSE gives us the exact match, whereas TRUE gives us the approximate match for the value if not in the lookup range. Vlookup True is used when we don’t get an exact match, but with the help of TRUE, we can get an approximate match or near value to the value which we are looking for.
How to Use Vlookup True?
To understand the use of Vlookup True, first, we need to understand the syntax of Vlookup. Below we have the syntax of Vlookup;
Where,
- Lookup_Value = Cell value whose value we need to find.
- Table_Array = Range or table from where we need to find the value of lookup_value.
- Col_Index_Num = sequence number of the column from which we want to get the value.
- Range_Lookup = 0 (or FALSE) for the exact match and 1 (or TRUE) for the approximate match.
We will be seeing the use of TRUE and FALSE range_lookup value and will see how Vlookup True works in the below examples.
Examples of VLOOKUP True
Lets us discuss the examples of VLOOKUP names.
Example #1
In this example, we will see the simple way to apply Vlookup True. For this, we have a list of alphabets, as shown below. As we can see, the alphabets are in proper sequence. And in separate cells, we will be looking up the values from the list and see the output.
In cell C2, we will put any of the alphabets we want to look up from Column A. Let’s consider it D.
In cell D2, insert the vlookup function and select the lookup value and range as needed.
To get the exact match, we can select FALSE range lookup or 0 for the exact match as shown below.
Once we enter, we can see that cell D2 will have the exact match value from the selected range.
Now, if we delete the Lookup Cell value D from the list available in column A, then Vlookup will not be able to look up anything and will return #N/A.
If we change the range lookup value of Vlookup syntax from FALSE to TRUE (or 1), we will get an approximate match for the lookup value D in cell D2 as shown below.
Once we exit the syntax, we will get the value nearer to the lookup cell value D in cell D2.
As we can see, the obtained value at cell D2 is C. This is because Vlookup TRUE gives the near about value if the exact match is unavailable. Here for the D, it returned the approximate match as C because C is the only near value before D.
Example #2
In this example, we will see how Vlookup True works with a larger data set. Below is a table where the names of the different persons, ages, and physique types are mentioned from Columns A to C. And there is another small table in column E: F where we will map the value from table 1.
As we can see, we have age and physique types for each person. Now, let’s find out the physique type of any age, say 26. Insert the vlookup at cell F2 as shown below.
Select the lookup cell E2 and the Lookup range from B2 to C8, as shown below.
Considering FALSE for range lookup, we will get the exact match.
Now, if we randomly change the age from 26 to 53 in cell E2 and again apply the lookup using TRUE range lookup criteria and see what we will get? As of now, when we change the cell E2 value from 26 to 53, we got #N/A, as there is no value in column C available for age 53.
Now we will apply the Vlookup function using TRUE.
Once we press enter or get exited from the Vlookup syntax, our physique type in cell F2 is GENERAL, as shown below.
The reason for getting General in cell F2 is the TRUE range lookup in vlookup syntax. As we did not have the age as 53, so Vlookup True returned us the value as General because, before 53, we got general physique in cell B4.
If we again change the value, let’s say 31. Then we will get a value closer to 31 per the table. Here we have 30 in cell B2.
Pros of VLOOKUP True
- It helps us get the approximate match if we do not have the exact value for the lookup cell.
- It is as simple as Vlookup False.
- Using this function is far better than using different IF condition functions.
Things to Remember
- It gives an approximate match.
- We can also use 1 in place of TRUE in the VLOOKUP syntax.
- For the numerical value, It looks up the value less than the value available in the lookup cell.
- Vlookup False gives the exact match, and if the match value is not available, then we will be getting #N/A
- #N/A means the lookup cell’s value is not found in the entire lookup range.
- The lookup range could be a table or column, but the value we will get from the column only.
Recommended Articles
This has been a guide to VLOOKUP True. Here we discuss How to Use VLOOKUP True, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
