Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)

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.


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.

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.

LOOKUP Function to Find Last Value in Column Greater Than Zero in Excel

Now take a look that we have found our last quantity which is 10.

LOOKUP Function to Find Last Value in Column Greater Than Zero in Excel


Similar Readings:


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.

LOOKUP + IF Functions to Find Last Non-zero Positive Value in Column

Note: You may need to press Ctrl+Shift+Enter instead of the Enter button in Excel older versions if you get an incorrect result.

Now you will see that the last value is defined by the combined functions.

How Does the Formula Work?

IF($C$5:$C$12<>0,$C$5:$C$12)
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}

LOOKUP(1000000,IF($C$5:$C$12<>0,$C$5:$C$12))
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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo