What You'll Learn / Key Take-Aways

- How the range_lookup argument in VLOOKUP controls exact versus approximate matches

- When to use TRUE (or 1) for fuzzy/range lookups instead of FALSE for exact matches

- How to structure a lookup table for tiered discounts and why it must be sorted ascending

- A working formula example and common pitfalls, including values smaller than the first threshold

- Pro tips and alternatives like XLOOKUP for modern Excel users

## What is the VLOOKUP range_lookup argument?

VLOOKUP has an optional final argument often called range_lookup. It accepts TRUE (or 1) for approximate match and FALSE (or 0) for exact match. Exact match finds a single identical value in the lookup column. Approximate match finds the closest value that is equal to or less than the lookup value. In practice, you will use exact match most of the time, but approximate match is essential for range-based lookups like discount schedules or tax brackets.

Definition: range_lookup

- FALSE or 0: exact match. VLOOKUP returns the row where the lookup value exactly equals a value in the first column of the table array.

- TRUE or 1: approximate match. VLOOKUP scans the first column of the table array from top to bottom and returns the value from the last row where the first-column value is less than or equal to the lookup value.

## Real-world example: discount schedule by order amount

Imagine you have a list of orders with amounts and you want to assign a discount based on tiers:

- $0.00 to $24.99: 0%

- $25.00 to $49.99: 5%

- $50.00 to $99.99: 10%

- $100.00 and up: 20%

Set up a lookup table that lists the minimum amount for each tier in the first column and the discount in the second column. For example, if your tier table occupies F1:G4 it would look like:

- F1: 0.00 G1: 0%

- F2: 25.00 G2: 5%

- F3: 50.00 G3: 10%

- F4: 100.00 G4: 20%

Use a VLOOKUP formula on each order amount. If the order amount is in B2, the formula to retrieve the discount is:

=VLOOKUP(B2, $F$1:$G$4, 2, TRUE)

Notes on that formula:

- $F$1:$G$4 is locked with absolute references so you can fill down. I typically press F4 after selecting the range.

- 2 is the column index to return the discount from the second column of the table array.

- TRUE makes the lookup approximate. VLOOKUP will find the largest value in column F that is less than or equal to B2.

If B2 is 82.68, the last value in column F that is less than or equal to 82.68 is 50.00. VLOOKUP returns the discount associated with 50.00, which is 10%.

## Key requirements for approximate match to work correctly

1. First column contains the lower bounds for each range. In other words, the table should list minimum or starting values for each tier.

2. The first column must be sorted in ascending order. VLOOKUP approximate match searches from the top and assumes ascending order. If it is not sorted ascending, results will be incorrect.

3. The lookup value and first-column values should be the same data type. If you mix text and numbers, you can get unexpected results.

4. If the lookup value is smaller than the first value in the first column, VLOOKUP returns #N/A.

## Why many rows returned #N/A with FALSE

If you try exact matching by using FALSE or 0, VLOOKUP will only return a result when the lookup value matches one of the values in the first column exactly. When using tiered numeric thresholds, most order amounts will not match exactly and will return #N/A. That is why approximate match is the correct choice for range lookups.

## Alternatives and modern functions

- XLOOKUP: If you have XLOOKUP available, it can perform similar approximate lookups with clearer syntax and extra features. Example: =XLOOKUP(B2, F:F, G:G, "", -1) using the appropriate match/search mode for next smaller item. Check your Excel version and syntax because XLOOKUP�s match_mode and search_mode vary.

- INDEX/MATCH: You can reproduce the same behavior with INDEX and MATCH using MATCH with match_type set to 1 for approximate match. Example: =INDEX(G:G, MATCH(B2, F:F, 1)). MATCH with 1 requires the lookup vector to be sorted ascending as well.

## Additional Tips, Pitfalls to Avoid & Pro Advice

- Always sort your lookup table ascending by the first column when using approximate match.

- Label the threshold values as minimums so it is clear what VLOOKUP is using for comparisons.

- Lock your table array with absolute references ($F$1:$G$4) before filling down to avoid accidental range shifts.

- If your dataset may contain lookup values smaller than the first threshold, handle the #N/A with IFERROR to return a default value or message. Example: =IFERROR(VLOOKUP(B2,$F$1:$G$4,2,TRUE), "No discount")

- Confirm data types. Numbers stored as text will not behave as expected in approximate lookups.

- Test boundary values. Try values that sit exactly on thresholds and just below them to confirm the results.

## FAQ

### What happens if my lookup table is not sorted when using approximate match?

VLOOKUP approximate match relies on ascending order. If the table is not sorted, VLOOKUP may return incorrect results because it scans top to bottom and stops at the last value less than or equal to the lookup value.

### Can I use VLOOKUP approximate match with text values?

Approximate match works best with numeric ranges. For text, approximate match attempts a lexicographical match and can be unreliable. Use exact match for text fields or consider other fuzzy matching methods for text similarity.

### How do I protect against lookup values smaller than my first threshold?

Wrap the VLOOKUP in IFERROR or pre-check the value. Example: =IF(B2 < $F$1, "Below range", VLOOKUP(B2,$F$1:$G$4,2,TRUE)). This prevents #N/A and makes behavior explicit.

### Is XLOOKUP better for approximate lookups?

XLOOKUP is more flexible and often clearer if you have it. It can return a default value when not found and offers more explicit match modes. But approximate match logic still benefits from sorted threshold tables.

### Why did only exact threshold values return when I used FALSE?

FALSE requires an exact match. With numeric tiers, most order amounts do not match the discrete threshold values and thus return #N/A. Use TRUE for range lookups.

## Summary of Key Points / Take-Home Messages

- Use range_lookup = TRUE (or 1) for range or fuzzy lookups where you want the largest threshold less than or equal to the lookup value.

- Structure your table with minimum values for each range and sort that first column ascending.

- Lock your table array before filling down, and handle values below the first threshold using IFERROR or a pre-check.

- Test boundary cases and consider modern functions like XLOOKUP for more flexibility.

This approach makes VLOOKUP work for real-world problems like discount schedules, tax brackets, and tiered pricing without forcing exact matches.