Formula syntax for MS Excel match next largest value

Asked By 0 points N/A Posted on -
qa-featured

Hello Friends,

I have a small technical clarification in MS Excel on how do I find the next largest value in MS Excel using 'lookup' or 'vlookup' option. Please guide me with the formula syntax for MS Excel match next largest value. Please do the needful.

Thanks and Regards,

Lawrence Elliott

SHARE
Best Answer by Laurie W Carpenter
Answered By 0 points N/A #193530

Formula syntax for MS Excel match next largest value

qa-featured

Hi Lawrence Elliott,

"Lookup" formula has some characteristics. One of them is that if "lookup" cannot find the lookup value, it will match the largest value of the other given values of the vector which is less than the lookup value. 

So, to find the next largest value use the number in the number in the formula which is just a unit fraction less than the previous number you have. I am giving you an example.

Use formula in Microsoft Excel

In the above frequency column Model H has the largest value 9.4

So, if you want to find the next largest value, put the lookup formula like this – 

=LOOKUP(9.3,A2:A9, B2:B9)

It will work to find the next largest value.

Good Day.

Best Answer
Best Answer
Answered By 0 points N/A #193531

Formula syntax for MS Excel match next largest value

qa-featured

Hi Lawrence Elliott,

If you are looking for a formula that would return the next larger value than the value you are looking for, assuming there is no exact match, then VLOOKUP and LOOKUP function would not be able to help. These two functions can only return the closest value that is LESSER the one you are looking for. 

The alternative to this is to use the INDEX + MATCH function combination. The key to this is the third argument of the MATCH function, which is the [match type], you have three options for this: Less Than, Exact match & Greater than. 

The last option is what you need to use, this will return the closest value that is greater than the value in question. You will then use INDEX to return the actual value because MATCH only returns the position of the value. One thing to note though, the values need to be arranged in DESCENDING order for this solution to work. 

See attached sample file that I have created for your reference.

Hope I have helped you, have a good day.

Regards,

Laurie

Related Questions