Usually we use vlookup for answering a particular question like “How many customers we had on January 1st 2014?” We expect an exact match and hence using FALSE as the last argument in the vlookup formula.
When do we use TRUE in vlookup then?
If we want to perform a vlookup using FALSE, we will need a table of at least 101 rows that lists the grade for each score from 0 to 100, like the one below:
wow… what a tedious work and a large table. Worse still, it cannot look up a score of e.g. 80.5 as this value is not in the table_array.
Here we go the TRUE vlookup with Approximate Match!
“If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.” – From Excel Help
Let’s look at cell F14 where the lookup_value is 79.9. As there is NO 79.9 in the 1st Column in the table_array, it returns the next largest value that is less than lookup_value, which is 70. And the corresponding result is “C”. Now it makes sense that the 1st column in the table_array has to be in ascending order, doesn’t it?
- If the lookup_value is less than the smallest (1st) number in the 1st column of the table_array, it returns “#N/A”
- In cell F12 “Six” is input & the Header row in the tabel_array (A10:B16), the vlookup compares “Six” with “From”. As “Six” is larger than “From” but smaller than “0”, it returns “Grade”.
- Hint: AVOID include header row in assigning the table_array for TRUE vlookup, i.e. Use $A$11:$B$16 instead
Other examples for using TRUE vlookup:
What is your case for using TRUE vlookup? Pls share with us by leaving a comment.
Other topics about vlookup:
- The basic of vlookup
- Tips in constructing vlookup
- vlookup with Match
- vlookup options – True or False?
- Advanced vlookup – Text vs. Number
- Advanced vlookup – Wildcard Characters “?” and “*”
- Alternative to vlookup – Index and Match
- Three different ways to do case-sensitive lookup