where “length” is the named range B5:B11, and “cost” is the named range C5:C11. So, when lookup values are sorted in ascending order, both of these formulas return “next smallest”: However, by setting match type to -1, and sorting lookup values in descending order, MATCH will return the next largest match. So, as seen in the example: returns 4, since 400 is the next largest match after 364.
Find associated cost
The full INDEX/MATCH formula to retrieve the associated cost in cell F8 is:
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.
