where “level” is the named range C5:C9, and “points” is the named range B5:B9. 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”:
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
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
