If you’ve ever worked extensively with Excel, chances are you’ve used the VLOOKUP function more times than you can count. But let’s be honest: how often do you find yourself using the “TRUE” argument? If you’re anything like me, you can probably count on one hand the number of times it’s come into play. Most Excel users stick to “FALSE” for an exact match, and with good reason. But there are situations where “TRUE” shines, albeit in a limited capacity.
In this post, I’ll walk you through when and why you might want to use “TRUE” in VLOOKUP, what to watch out for, and whether there are better alternatives. I’ll even share a story of the last time I used it—a rare occurrence, but one worth noting.
The Basics of TRUE in VLOOKUP
When you use VLOOKUP, you’re typically searching for a value in the first column of a table and returning a corresponding value from another column. The final argument, “range_lookup,” determines whether you’re looking for an exact match (“FALSE”) or an approximate match (“TRUE”).
Here’s the key difference:
- FALSE (Exact Match): Looks for a specific value. If it doesn’t find it, you get an error.
- TRUE (Approximate Match): Searches for the closest match that is less than or equal to the lookup value. The data in the first column must be sorted in ascending order for this to work correctly.
Why Most People Rarely Use TRUE
Most scenarios call for exact matches: product IDs, customer numbers, employee names, etc. Using “TRUE” is more niche. For example, you might need it for:
- Tax brackets
- Grading scales
- Commission tiers
These are all cases where you’re not looking for a direct match but rather the closest value below your lookup value.
The Last Time I Used TRUE in VLOOKUP
Let me share my last time using the TRUE in VLOOKUP. Not long ago, I was helping a coworker with his task. He needed to calculate discounts for a tiered pricing structure. To preserve confidentiality, I’ve recreated a similar table to illustrate the concept:
| Quantity | Discount |
| 10 | 5% |
| 50 | 10% |
| 100 | 15% |
| 200 | 17% |
| 300 | 18% |
| 500 | 20% |
The goal was to determine the discount for a given quantity of items ordered. For example, if a coworker needed to calculate the discount for 120 items, the expected result would be 15% (the closest match below 120). Using “TRUE” in VLOOKUP was the perfect solution:
=VLOOKUP(120, A2:B7, 2, TRUE)
The result was 15%, as expected. Simple, efficient, and exactly what was needed. Of course, I had to double-check that the data was sorted properly—a crucial step when using “TRUE.”
What to Keep in Mind When Using TRUE
- Sort the Data: The first column of your lookup table must be sorted in ascending order. If it’s not, you’ll get unpredictable results.
- Beware of Overlapping Ranges: If your table has duplicate or overlapping values in the first column, “TRUE” might not behave as expected.
- Fallback for Missing Values: If the lookup value is smaller than the smallest value in the table, VLOOKUP with “TRUE” will return an error.
- Understand the Logic: “TRUE” doesn’t look for an exact match. It stops at the largest value less than or equal to your lookup value, which is crucial to remember.
Alternatives to Using TRUE in VLOOKUP
While “TRUE” can be handy, there are alternatives. Let’s explore how these can work with the above table. To better understand the nuances of these methods, I created a new dataset with the following actual quantities: 120, 0, 600, 310, 90, and 400
1. INDEX-MATCH Combination
The INDEX-MATCH combination can replicate approximate matches without relying on sorted data. Here’s how it works:
=INDEX($B$2:$B$7, MATCH(D2, $A$2:$A$7, 1))
- MATCH(D2, $A$2:$A$7, 1)): Finds the largest value less than or equal to the actual quantity in the Quantity column.
- INDEX($B$2:$B$7, result): Returns the corresponding Discount.
This setup ensures flexibility and works seamlessly if the data remains sorted.
2. IF Statements
For smaller datasets, nested IF statements can handle ranges effectively. Using the same example:
=IF(D2 >= 500, 20%, IF(D2 >= 300, 18%, IF(D2 >= 200, 17%, IF(D2 >= 100, 15%, IF(D2 >= 50, 10%, 5%)))))
While not as scalable, this approach is clear and doesn’t require sorting.
3. LOOKUP Function
LOOKUP offers a simpler alternative for approximate matches. Unlike VLOOKUP, LOOKUP doesn’t require a sorted table when using an array format. Here’s an example:
=LOOKUP(D2, {10,50,100,200,300,500}, {0.05,0.1,0.15,0.17,0.18,0.2})
This formula looks for the largest value less than or equal to the actual quantity in the first array and returns the corresponding value from the second array.
Exploring the New Dataset
| Actual Quantity | VLOOKUP (TRUE) | INDEX-MATCH | LOOKUP | IF Statement |
| 120 | 15% | 15% | 15% | 15% |
| 0 | #N/A | #N/A | #N/A | 5% |
| 600 | 20% | 20% | 20% | 20% |
| 310 | 18% | 18% | 18% | 18% |
| 90 | 10% | 10% | 10% | 10% |
| 400 | 20% | 20% | 20% | 20% |
The issue becomes evident with the value 0. Most methods (VLOOKUP, INDEX-MATCH, and LOOKUP) return #N/A because the value is smaller than the smallest value in the lookup table. However, the IF statement provides a fallback value of 5%, which corresponds to the lowest range.
Addressing the Problem
To handle cases where the lookup value is below the minimum range, you can introduce a fallback mechanism. Here’s how each method can be adjusted:
1. VLOOKUP with a Fallback
Use the IFERROR function to handle errors:
=IFERROR(VLOOKUP(J2, $A$2:$B$7, 2, TRUE), $B$2)
This formula ensures that if VLOOKUP returns an error (e.g., for 0), it defaults to the smallest discount in the table (5%).
2. INDEX-MATCH with a Fallback
Wrap the MATCH function with IFERROR:
=IFERROR(INDEX($B$2:$B$7, MATCH(J2, $A$2:$A$7, 1)), $B$2)
This approach behaves similarly to the VLOOKUP adjustment.
3. IF-LOOKUP Combination with a Fallback
While LOOKUP is less flexible with error handling, you can use a conditional structure:
=IF(J2 < MIN($A$2:$A$7), $B$2, LOOKUP(J2, $A$2:$A$7, $B$2:$B$7))
This checks if the value is below the minimum range and returns the lowest discount if true.
My Recommendation
If you’re working with tiered data and need to determine approximate matches, “TRUE” in VLOOKUP can be a quick solution—but only if the data is sorted and straightforward. For more complex or dynamic scenarios, I recommend using INDEX-MATCH or LOOKUP for added flexibility and reliability. These alternatives provide better control and reduce the risk of errors caused by unsorted data.
Also, if your dataset might include values smaller than the lowest tier, I recommend using either VLOOKUP or INDEX-MATCH with a fallback mechanism. These approaches provide the most straightforward and reliable results. The IF statement is also a good option for smaller datasets, especially when you want to explicitly define all ranges and their corresponding outputs.
By tailoring your approach to the dataset’s requirements, you can ensure accurate and robust results. Ultimately, the choice depends on your specific needs and dataset. But as always, test thoroughly to ensure accuracy!