How to Lookup the Next Largest Value

This post explains that how to lookup the next largest value in another column in excel. how to find the next largest value in a range with the excel formula.

Find the next largest value

If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula.

For example, if we used the VLOOKUP function to lookup the value 200, then it returns “103”, so it returns the value that is less than or equal to the lookup value. but if you want to return the next largest value in the range A2:A5, you can use the following formula:

=INDEX(A2:A5,MATCH(200,A2:A5)+1)

And if you want to find the next largest value in another column B2:B5, you just need to change the first argument of the INDEX function, use the range B2:B5 instead of A2:A5, the formula is as follwos:

=INDEX(B2:B5,MATCH(200,A2:A5)+1)

Let’s see how the above formula works:

=MATCH(200,A2:A5)+1

lookup next largest value 1

The MATCH function return the position of the lookup value 200 in the range A2:A5, if the MATCH function is not find the exact match, then it would return an approximate matched value(it should be less than or equalt to the lookup value) by default, so it returns 1.

To get the position of the next largest value, the position number returned by the match function need to add 1 again. so now we got the position of the next largest value in the range A2:A5. It returns 2.

=INDEX(A2:A5,MATCH(200,A2:A5)+1)

lookup next largest value2

=INDEX(B2:B5,MATCH(200,A2:A5)+1)

lookup next largest value3

The INDEX function returns the value based on the given position that returned by the above MATCH function.

so if you used the Range A2:A5, then the above INDEX function returns the next largest value in the range A2:A5.

And if you used the Range B2:B5, then it returns the next largest value in the range B2:B5.

 

You can also use the following array formula to achieve the same result.

=INDEX(B2:B5,MATCH(TRUE,A2:A5>200,0))

lookup next largest value4

Note: when you enter the above array formula into a single cell, you should press Ctrl + Shift +Enter key.


Related Formulas

  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Reverse a List or Range
    If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula. you can use the following formula:=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)…

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel VLOOKUP function
    The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar