VLOOKUP True (Table of Contents)
VLOOKUP with True
It is obvious that when we apply Vlookup function, we always look for the exact match. When we get the #N/A, then also we assume that the value which we are looking for is not available in the lookup table. But we never have tried using Vlookup with TRUE value match. 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. We can 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 lookup range.
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.
Now in cell C2, we will put any of the alphabets which we want to lookup from Column A. Let’s consider it D.
Now in cell D2, insert the vlookup function and select the lookup value and lookup range as needed.
To get the exact match, we can select FALSE range lookup or we also choose 0 for the exact match as shown below.
Once we enter, we will be able to see the cell D2 will now has 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 lookup anything will return #N/A.
Now, if we change the range lookup value of Vlookup syntax from FALSE to TRUE (or 1), then we will get an approximate match for the lookup value D in cell D2 as shown below.
Once we exit from the syntax, we will get the value nearer to lookup cell value D in cell D2.
As we can see, the obtained value at cell D2 is C. Which is because Vlookup TRUE gives the near about value if the exact match is not available. Here for the D, it returned the approximate match as C because C is the only near value that comes before D.
Example #2
In this example, we will see, how Vlookup True works when we have a larger set of data. Below, we have a table where the name of the different persons along with age and physique type is mentioned from Column A to C. And there is another small table at column E:F where we will be mapping the value from table 1.
As we can see the for each person, we have age and physique type. Now, let’s try to find out the physique type of any of the age, say 26. Insert the vlookup at cell F2 as shown below.
Now select the lookup cell E2 and Lookup range from B2 to C8 as shown below.
Considering FALSE for range lookup, we will get the exact match.
Now if we change the age from 26 to 53 randomly 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 the TRUE.
Once we press enter or get exited from the Vlookup syntax, then are physique type in cell F2 as GENERAL as shown below.
The reason behind, getting General in cell F2 is because of TRUE range lookup in vlookup syntax. As we did not have of 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 say 31. Then we will be getting the value which is closer to 31 as per the table. Here we have 30 in cell B2.
Pros of VLOOKUP True
- It helps us to get the approximate match if we do not have the exact value available for 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 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 that the lookup cell’s value is not found in the entire lookup range.
- Lookup range could be table or column, but the value which 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 along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
