Paramix Learning: Look Up Next Bigger Value In Excel

Look Up Next Bigger Value in Excel

By: Eddie Baki, 15.1.2013, 11:23

How do we get Excel to deliver the next bigger value. The video below shows you how by assigning a boxer’s weight to the corresponding weight class.

The Excel function Match is used to get the next bigger value of a searched value. Match required three parameters: Lookup value, lookup array and match type.

The lookup value is the value you wish to have looked up. In our example it is the boxer’s weight.

Lookup array is basically your dictionary. It is the list of values you search in to find the closest value to your lookup value. In the video’s example the lookup array is basically the list of weights.

Match type, the final parameter of match, requires one of three values: 1, 0 or -1.

To get the next bigger value, match type has to be -1. Remember to also sort your lookup array in descending order, otherwise match would not work.

Setting match type to 0 returns an exact match, and setting it to 1 returns the next smaller value. When setting match type to 1 you have to sort your lookup array in an ascending order for match to work.

After inputting all of the match function’s parameters, match returns an integer. This number is the line number in the array in which your desired result resides.

In order to get the desired result, we need a second function: index

Index requires three parameters: array, row number and column number

Array is the table containing all the data.

Row number what the function match delivers.

Column number selects the column in which your desired results resides.

Please use the feedback link below for comments and suggestions.

Your Feedback

Your E-mail