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:
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
