Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles LOOKUP Next Largest Match with the MATCH Function

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc