Next Largest Match in Excel: How to Find It

The next largest match in Excel is found with the MATCH function using match_type -1. You enter the lookup value, the lookup array, and -1, and Excel returns the position of the smallest value that is greater than or equal to your lookup value. This is the opposite of the more common exact match (0) or the next smallest match (1).

Formula

The MATCH function with match_type -1 uses this logic:

MATCH(lookup_value, lookup_array, -1)

• lookup_value – the number you are searching for (e.g., 25).
• lookup_array – a range of cells or a list of numbers that must be sorted in descending order (largest to smallest).
• -1 – tells Excel to find the smallest value that is greater than or equal to the lookup value.

The result is the position (row or column index) of that value, not the value itself. If you need the actual value, combine MATCH with INDEX: INDEX(array, MATCH(lookup, array, -1)).

Worked Example

Using the default values from the calculator block:

• Lookup value = 25
• Lookup array = {50, 40, 30, 20, 10}

Step-by-step:

1. Sort the array in descending order: 50, 40, 30, 20, 10 (already done).
2. Scan from the largest to the smallest: 50 (≥25), 40 (≥25), 30 (≥25), 20 (not ≥25 – stop).
3. The last value that is ≥25 is 30, which is in position 3 of the array.

So MATCH(25, {50,40,30,20,10}, -1) returns 3. That is the position of the next largest match (30).

When to Use Match Type -1

Use -1 when you need to find the next largest value that is still greater than or equal to your lookup value. This is common when:

• You have a list of thresholds (e.g., discount tiers, tax brackets, shipping weights) and want to find the applicable tier for a given input.
• You are looking up values in a table that is sorted from highest to lowest, such as a price list where you want the closest higher price.
• You need to perform an approximate match in a descending sorted list, similar to VLOOKUP with range_lookup TRUE but in reverse order.

For example, if you have shipping costs for weights 10, 20, 30, 40 kg, and you want the cost for a 25 kg package, MATCH with -1 gives you the position of the 30 kg row – the next heavier weight.

How to Choose the Right Match Type

Excel’s MATCH function has three match types:

• 0 – exact match. The array does not need to be sorted. This is the most common and works when you have unique IDs or names.
• 1 – next smallest match. Finds the largest value that is less than or equal to the lookup value. Requires the array to be sorted in ascending order.
• -1 – next largest match. Finds the smallest value that is greater than or equal to the lookup value. Requires the array to be sorted in descending order.

If you are unsure which to use, ask yourself: “Do I want the closest value that is smaller (1) or larger (-1) than my lookup?” If you need an exact match, use 0. Sorting is the key – using -1 on an ascending list will give wrong results.

Common Mistakes and Pitfalls

• Forgetting to sort descending – This is the #1 error. If the array is not sorted from largest to smallest, MATCH with -1 may return a #N/A error or an incorrect position.
• Confusing position with value – MATCH returns the index number, not the value. Use INDEX to get the actual value.
• Using -1 when you need 1 – They are opposites. -1 gives the next largest, 1 gives the next smallest.
• Rounding issues – If your lookup value is 25.1 and the array has 25, -1 will return the position of the next value above 25.1 (e.g., 30). Be aware of decimal precision.
• #N/A error – This occurs when the lookup value is smaller than the smallest value in the array (for -1) or larger than the largest (for 1).

Real-World Example: Shipping Cost Lookup

Suppose you have a shipping rate table:

• Weight (kg): 5, 10, 20, 30, 50
• Cost ($): 10, 15, 25, 35, 50

You want to find the cost for a package weighing 18 kg. Since the weight list is in ascending order, use match type 1 (next smallest) to get the 10 kg row, or re-sort descending and use -1 to get the 20 kg row. The choice depends on your business rule: do you charge for the next heavier tier or the previous lighter one?

Using -1 with a descending list: MATCH(18, {50,30,20,10,5}, -1) returns 3 (the 20 kg row), and INDEX({10,15,25,35,50}, 3) gives $25.

Limitations and Alternatives

MATCH with -1 only works on sorted data. If your data is unsorted, you cannot use approximate match – you would need to use an array formula like MIN(IF(array>=lookup, array)) (entered with Ctrl+Shift+Enter in older Excel) or the newer XLOOKUP function with match_mode -1 (next larger). XLOOKUP is available in Excel 365 and 2021, and it does not require sorting:

XLOOKUP(lookup_value, lookup_array, return_array, , -1) – this finds the next larger value without sorting. However, XLOOKUP is not available in older versions, so MATCH remains the standard for compatibility.

Frequently Asked Questions

What does match_type -1 do exactly?

It finds the position of the smallest value in the array that is greater than or equal to the lookup value. The array must be sorted in descending order for the result to be correct.

Why does MATCH return #N/A with -1?

This happens when the lookup value is smaller than the smallest value in the array. For example, if your array is {50,40,30} and you look up 10, there is no value ≥10, so Excel returns #N/A.

Can I use -1 with unsorted data?

No. The result will be unreliable. If your data is unsorted, use XLOOKUP with match_mode -1 (if available) or an array formula to find the next larger value.

What is the difference between match_type 1 and -1?

Match type 1 finds the largest value that is less than or equal to the lookup value (next smallest) and requires ascending sort. Match type -1 finds the smallest value that is greater than or equal to the lookup value (next largest) and requires descending sort.

How do I get the actual value instead of the position?

Wrap MATCH inside INDEX: INDEX(return_range, MATCH(lookup_value, lookup_array, -1)). This returns the value from the corresponding row or column.

Is XLOOKUP better than MATCH for next largest?

XLOOKUP is more flexible because it does not require sorting and offers a direct match_mode -1. However, MATCH is still widely used for compatibility with older Excel versions and for simple index lookups.

Related

• WoW Classic vs Retail: Which Azeroth Should You Choose?
• Nokia 6.1 Plus vs Xiaomi Mi A2: Mid-Range Battle
• AI Security vs Traditional App Security: Key Changes

in lookup | Lookup value | - | 25 | 0
in arr | Lookup array (sorted descending) | - | 50, 40, 30, 20, 10 | 0
out pos | Position (next largest) | - | MATCH(lookup, arr, -1) | 0