Go Back

Next Largest Match with the MATCH Function

We can use the MATCH function to lookup the next largest match in a given set of values. This function is used in an approximate match mode, with -1 being for the match type. This post provides a clear guide on how to use the MATCH function to lookup the next largest match in a set of values.

Figure 1. Final result

Syntax of the formula

=MATCH (value, array, -1)

Explanation

Normally, the MATCH function matches the next smallest value in a given list which has been arranged in an ascending order. It will then move to the next larger value than the lookup value, and then drop back to the previous value. When values have been sorted in an ascending order, the formulas below will return “next smallest”:

=MATCH (value, array) // default

=MATCH (value, array, 1) // explicit

The MATCH function will return the next largest match if the match type is set to -1, and the values arranged in a descending order.

In our example above, we have the formula in cell E3 as;

=MATCH(E1,A2:A11,-1)

Note that length is A2:A11, while cost is B2:B11.

Things to remember

While utilizing the match function, the following things are necessary to remember;

When the value set has been sorted in an ascending order, we shall need a match_type 1 at the end.

If the value set does not need any sorting, then you need a match_type 0.

We use a match_type -1 when the value set we are dealing with has been sorted in a descending order.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

The last argument in the VLOOKUP command is optional and shoould be either TRUE or FALSE. What is returned when the argument is TRUE? An exact or approximate match is returned. If an exact match is not found, the next smallest value that is more than lookup_value is returned.The data must be sorted in ascending order An exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.The data must be sorted in ascending order An exact or approximate match is returned. If an exact match is not found, N/A is returned. The data can be in any order. An exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.The data need not be sorted in any order
Solved by B. U. in 27 mins
I want to find the largest date in nth row. But my n is expressed in MATCH() form. I tried LARGE(MATCH(A4,A:A, 0):MATCH(A4,A:A, 0), 1). Only works when large(N:N,1)
Solved by I. J. in 14 mins
Need help with Index Match function.
Solved by G. Y. in 27 mins

Leave a Comment

avatar