How to Find the Last or First Value in a Range Greater Than X in Excel

This post will guide you how to find the first value greater than a specified value in a range of cells in Excel. How do I find the last value greater that a given value in a row with a formula in Excel.

Assuming that you have a list of data in range B1:B6, in which contain numeric values. And you want to find the first number from the list that is greater than a given number (30). Or you wish to find the last one number from your data that is greater than a number 30. The below will show you one formula to accomplish it.

Find the Last Value Greater Than X


If you want to identify the last value in your range B1:B6 that is greater that number 30, you can use the following array formula based on the LOOKUP function and the IF function. Like this:

=LOOKUP(9.999999999E+307,IF(B1:B6>30,B1:B6))

You can type the above formula into a blank cell in your worksheet, and make sure to press Ctrl +shift +Enter keys on your keyboard to change your formula as array formula.

find last value greater thatn x1

Find the First Value Greater Than X


If you want to find the first value in a range that is greater that number 30, you can use the following formula based on the INDEX function and the MATCH function to identify the first value in the range B1:B6 greater that number 30. Like this:

=INDEX(B1:B6,MATCH(TRUE,INDEX(B1:B6>30,0),))

You just need to type the above formula into a blank cell in your worksheet, and make sure to press Ctrl +shift +Enter keys on your keyboard to change your formula as array formula.

find last value greater thatn x2

Related Functions


  • Excel LOOKUP function
    The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • 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 MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

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

Sidebar