Quick, clean, and to the point

Match next highest value

Excel formula: Match next highest value
Generic formula 

To match the "next highest" value in a lookup table, you can use a formula based on INDEX and MATCH. In the example shown, the formula in F6 is:


where "level" is the named range C5:C9, and "points" is the named range B5:B9.


This formula is a standard version of INDEX + MATCH with a small twist.

Working from the inside out, MATCH is used find the correct row number for the value in F4, 2100. Without the third argument, match_type, defined, MATCH defaults to approximate match and returns 2.

The small twist is that we add 1 to this result to override the matched result and return 3 as the row number for INDEX.

With level (C5:C9) supplied as the array, and 3 as the row number, INDEX returns "Gold":

=INDEX(level,3) // returns Gold

Another option

The above approach works fine for simple lookups. If you want to use MATCH to find the "next largest" match in more traditional way, you can sort the lookup array in descending order, and use MATCH as described on this page.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.