Formula syntax for MS Excel match next largest value

Asked By lawrence elliott 0 points N/A Posted on - 02/23/2013

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

• Status: Open
• Question Views: 882
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Laurie W Carpenter

Go To Solution

Answered By Darren S Rigney 0 points N/A #193530

Formula syntax for MS Excel match next largest value

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.

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.

About Darren S Rigney

Questions
5

Answers
14

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 03/10/2013
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Laurie W Carpenter 0 points N/A #193531

Formula syntax for MS Excel match next largest value

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

About Laurie W Carpenter

Questions
7

Answers
11

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 03/14/2013
• Question Category: MS Excel