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 Expert are available now. Your privacy is guaranteed.

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.

Solution examples
I need help with a vlookup type formula that will take a given name and reference it against a list of names in columns D-O. then take a given date and reference it against dates in columns A10-A440, then return the cell or its contents at the intersection of those that column and row.
Solved by I. H. in 13 mins
How do I prevent Duplicate entries in multiple columns in Excel using data validation in MSExcel 2016. I would like to prevent the user from data entering a code more than once in three column ranges
Solved by B. D. in 60 mins
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
I have a file in which I need to somehow use a conditional formatting for the same names of the data available, or if there is any other way to calculate the frequency of one particular name appearing.
Solved by K. F. in 51 mins
I need a formula to compare the data in two columns and then export the mismatched data in the 3rd column
Solved by S. Q. in 20 mins