Excel: Find First Value Greater than Specific Number


You can use the following formula in Excel to find the first value in a column greater than a specific number:

=INDEX(B2:B11,MATCH(TRUE,INDEX(B2:B11>20,0),))

This particular formula finds the first value in the range B2:B11 with a value greater than 20 and returns the value.

To find the first value greater than a different specific number, simply change the 20 in the formula to a different number.

The following example shows how to use this formula in practice.

Example: Find First Value Greater than Number in Excel

Suppose we have the following dataset in Excel that shows the number of points scored by basketball players on various teams:

Suppose we would like to find the first value in the Points column that is greater than 20.

We can type the following formula into cell C2 to find this value:

=INDEX(B2:B11,MATCH(TRUE,INDEX(B2:B11>20,0),))

The following screenshot shows how to use this formula in practice:

Excel find first value greater than

The formula returns a value of 24, which is the first value in the Points column that is greater than 20.

Note that if you would instead like to return the name in the Team column that corresponds to the first value in the Points column with a value greater than 20, you can simply change the first range in the formula from B2:B11 to A2:A11:

=INDEX(A2:A11,MATCH(TRUE,INDEX(B2:B11>20,0),))

The following screenshot shows how to use this formula in practice:

The formula now returns a value of “Nets”, which represents the first team with a points value greater than 20.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Find First Non-Zero Value in Row
Excel: How to Find Top 10% of Values in Column
Excel: How to Find Top 10 Values Based on Criteria

One Reply to “Excel: Find First Value Greater than Specific Number”

  1. Evening,

    I have tried to use this formula to select the value that is greater the closest value above what is in cell I10 from a list of values on another sheet yet it just keeps returning the biggest value from that list any help:
    =INDEX(UKB!R11:R117,MATCH(TRUE,UKB!R11:R117>’Beam Calculations’!I10,0))

    Thanks, Millie

Leave a Reply

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