Find bottom cell value that is not 0

[Pavertheway]

Hi,

I have a spreadsheet where I need to locate the bottom-most cell that's value is NOT 0 - however there is a possibility that the value we are looking for is numerically below 0.

I found;
LOOKUP(9.999999999999E+307,IF(Q1:Q300>0,Q1:Q300))

Which locates the bottom most cell with a value that is greater than 0, but does not count a negative value as it is below 0.

Is there a way to do what I need?

Thank you in advance.

 

[JustynaMK]

Hi,

Maybe try something like this (a small change):
=LOOKUP(9.999999999999E+307,IF(Q1:Q300<>0,Q1:Q300))

Enter it with Ctrl+Shift+Enter (array formula).

 

[Pavertheway]

That worked perfectly, thank you so much!

Now, is there any way to use that formula to identify a cell, and copy the value of the cell TO THE RIGHT?

Thank you for your help so far

 

[Pavertheway]

Hi,

I got it -

=OFFSET(INDEX($Q$4:P100,MATCH(0,$Q$4:P100,0)-1),0,1)

The array formula I couldn't get to work as part of an Offset formula but this works perfectly.

Thanks for your help!

 

[JustynaMK]

good news. Take care!