If there is a series of numbers and you are interested to find the sum of smallest n (e.g. smallest 5 values) values then this article will help you do this with a generic formula that can be used for any list of numbers.
If a value is given and it is to be matched from a lookup table then this can be done with the help of MATCH formula. Moreover, the corresponding entry based on the matched value can be returned with an additional INDEX function. The generic formula for getting the next highest value is given below.
The above-written formula uses INDEX function which is passed with a MATCH function as its second parameter.
How the Match Next Highest Value’ Formula Works
The task to match the next highest value from an array by using INDEX and MATCH formulas with a little twist.
MATCH function used here accepts two arguments lookup value and lookup array respectively. The first argument specifies the value that must be looked for and returns a number representing the position in lookup array.
The formula for matching the next highest value actually an INDEX function which uses data array as the first argument and MATCH function as second. MATCH function is not provided with 3rd argument which defaults it to approximate match and return a value. The small trick is that we add 1 to the result returned to override the matched result and return the next value as the row number for INDEX.
Four different levels are considered in an Excel sheet each corresponding to some specific points. In the specification criteria, 2100 is entered as an entry that needs to be matched. Now two searches can be made one for matching the closest value and the next to match the next highest value.
Figure1. Sample sheet for matching the next highest value
To get the closest match to the 2100 points in cell F2. Enter the following formula:
Figure2. Matching the closest value
To match the next highest value, enter the same formula as above with only a difference that adds one to the 2nd parameter of INDEX function (outside of MATCH function) to override the result returned by MATCH.
The formula entered in F4 is as follows:
Figure3. Matching the next highest value from data
The results can be seen in the following snapshot:
Figure4. Getting the result to match the next highest value
It can be observed that corresponding to 2100 points, the closest match in points array is 2000, which returns the level Intermediate. In order to get the next highest value of 2100. The search is again performed with +1 offset in the 2nd argument of INDEX which corresponds to 3000 and returns Expert.