You can use the following formula in Excel to find the last value in a column that is greater than zero:
=LOOKUP(2,1/(A2:A13>0),A2:A13)
This particular formula finds the last value in the range A2:A13 that is greater than zero.
The following example shows how to use this formula in practice.
Example: Find Last Value in Column Greater than Zero in Excel
Suppose we have the following column of values in Excel:
Suppose we would like to find the last value in the range A2:A13 that is greater than zero.
We can type the following formula into cell C2 to do so :
=LOOKUP(2,1/(A2:A13>0),A2:A13)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 12.
By looking at the data, we can confirm that this is indeed the last value in the column greater than zero:
If we change one of the values below the 12 to be greater than zero, then the formula will automatically update to detect this new value.
For example, suppose we change the value in the second to last row to 15.
The formula automatically updates to detect that this is the last value in the column that is greater than zero:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Find First Occurrence of a Value in Column
Excel: Find First Value Greater than Specific Number
Excel: How to Find First Non-Zero Value in Row
