Md. Sourov Hossain Mithun
We may need to find the last entry, last purchase, or last order greater than zero while using MS Excel for different purposes. There are many ways to do it, but today I’ll provide two easy formulas that will help you to find the last value in a column that is greater than zero in Excel.
Below we are showing a sample method where we used the LOOKUP and IF functions to find the last value which is positive in a column.
Download Practice Book
You can download the free Excel template from the following download button and practice on your own.
2 Excel Formulas to Find the Last Value in Column That is Greater than Zero
So, let’s introduce our sample dataset first. Here, I have placed some fruits’ quantities and their unit prices in my dataset. Now, we’ll apply two formulas to find the last value in Column C that is greater than zero. In order to avoid any compatibility issues, try to use the Excel 365 edition.
1. Use LOOKUP Function to Find Last Value in Column Greater Than Zero in Excel
We can use The LOOKUP function in Excel to look through a single column or row to find a specific value from the same place in a second column or row. We often use this function when there are multiple worksheets within a workbook or a large amount of data in a worksheet. We’ll use it here in our first method to find the last non-zero quantity in Column C. So, let’s see how to do it.
Steps:
- Activate Cell D14.
- Then type the following formula:
=LOOKUP(2,1/(C5:C12>0),C5:C12)- Now, just hit the Enter button to get the output.
- Now take a look that we have found our last quantity which is 10.
Similar Readings:
- How to Find Multiple Values in Excel (8 Quick Methods)
- Find from Right in Excel (6 Methods)
- How to Find Character in String Excel (8 Easy Ways)
- Excel Find Last Column With Data (4 Quick Ways)
2. LOOKUP + IF Functions to Find Last Non-zero Positive Value in Column
In this method, we’ll do the task in a different way. Let’s combine two functions- the LOOKUP function and the IF function and find the last value in a column that is greater than zero. The IF function is a logical function to return one value if a condition is true and another value if it’s false.
Steps:
- Activate Cell D14 by clicking there.
- Then write the given formula-
=LOOKUP(1000000,IF($C$5:$C$12<>0,$C$5:$C$12))- Press the Enter button to get the result.
- Now you will see that the last value is defined by the combined functions.
⏬ How Does the Formula Work?
The IF function will check the array C5:C12 that is there any zero value or not. If it finds a non-zero value, then it will show that value and if finds zero then it will show FALSE. The output will return as-
{5;7;10;4;3;20;10;FALSE}
Finally, the LOOKUP function will search the large number which we have provided through the output of the IF function. We have used that large number intentionally because we know it won’t find that number so finally, it will do an approximate match and will show the last non-zero value. That will return as-
10
Conclusion
I hope all of the methods described above will be good enough to find the last value in a column greater than zero in Excel. Feel free to ask any questions in the comment section and please give me feedback.
Related Articles
- How to Find a Character in String in Excel
- Find External Links in Excel (6 Quick Methods)
- How to Find If A Range of Cells Contains Specific Text in Excel (4 Methods)
- Excel Search for Text in Range (11 Quick Methods)
