Find Last Value in Column Greater than Zero in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of how we can find the last value in column greater than zero.


How to Find the Last Value in Column That is Greater than Zero in Excel: 2 Formulas

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.

Sample dataset showing the last value 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.

use if LOOKUP Function to Find Last Value in Column Greater Than Zero

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

Drag down the Fill Handle to cell D12

Read More: How to Find First Value Greater Than in Excel


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.

  • 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

 

 


Download Practice Book

You can download the free Excel template from the following download button and practice on your own.


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


<< Go Back To Excel Last Value in Range | Excel Find Value in Range | Excel Range | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo