Next largest match with the MATCH function

The MATCH Function is categorized as a Lookup/Reference Function. It helps to determine the “next largest” match in an array of values. Basically it looks for a specified item in a range of cells, and then calculates the relative position of that specific item within the given range.

Let’s say, you have a range of cells C2:C4 contains certain numbers, 195, 65, and 97, then the Match Syntax=MATCH(97,C2:C4,0) gives us the number 3, because 97 is the third item in the given range C2:C4

Figure 1. Example 1 of The MATCH Function

Generic formula


How to find the Next largest match using the Match Function

The MATCH Function makes it easier for you to determine the relative ranking of a value in an array in Excel. The Match Syntax incorporates the following significant characteristics;

  • value (look up) is recognized as the given object you would like to try and find within a considerable number of objects. It is imperative for the Match Function to operate.
  • the array is the complete arrangement of the objects that are made up of the given object that we are now making an attempt to identify. It is equally important for the Match Function to determine the result.
  • match type is the value that most closely fits the identity of the object you are interested in. The match type value is only able to contain one of three options, -1, 0 and 1. Should you not submit it,  Excel will by default, deduce a value of 1.

With the assistance of the MATCH function you are really in simple terms, attempting to figure out the location of your lookup value within the area of cells you are searching through (the lookup array)

Figure 2. Example 2. of the MATCH Function.


In the example above, it is important to note that Weight=A2:A8  and Price=B2:B8

Practical knowledge of the match_type parameter is vital to implementing the MATCH function without difficulty.

  • A match type value 1 is required to be included at the time your list is structured in ascending order (least to highest)
  • In the event that your match type is 0, its simple and easy translation is that your list will likely not have a need for sorting.
  • A match_type value of -1 is required to be made use of in cases where your list is organized in descending order (greatest to least)
  • Despite the fact that Excel never fails to assume that the match type value is 1, you are still strongly recommended to key-in your own match value when utilizing the Match Function syntax mainly because you would quite possibly not always obtain the outcome you were anticipating. 
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment