Explanation
The default behavior of the MATCH function is to match the "next smallest" value in a list that's sorted in ascending order. Essentially, MATCH moves forward in the list until it encounters a value larger than the lookup value, then drops back to the previous value.
So, when lookup values are sorted in ascending order, both of these formulas return "next smallest":
=MATCH(value,array) // default
=MATCH(value,array,1) // explicit
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:
=MATCH(F6,length,-1)
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:
=INDEX(cost,MATCH(F6,length,-1))
Related formulas
Match next highest value
INDEX and MATCH approximate match
Related functions
INDEX Function
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
MATCH Function
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
Related videos
