Is there a function to "find previous"?

[canyflyer]

Hi all,
Im currently working on a sheet that is data taken from a PDF file.
It's alot of information so the data is in a set format that I dont have time to sit and change.
One of the things im trying to do is to tell a cell:
=if(W1398>0,"find the most previous cell in column N and put the value here",)
Any ideas on how to do this?
Thanks in advance.

 

[T. Valko]

Brilliant! Works like a charm!
Quite a head u got there thank you very much!

May I just ask, if it's not too much trouble, how that works?

Let's use this small data sample.

A2 = 10
A3 = 20
A4 = 5
A5 = 0

We want the last number in the range that is not 0.

=LOOKUP(2,1/A2:A5,A2:A5)

=5

The way that LOOKUP works is if every value in the lookup_vector 1/(A2:A5) is less than the lookup_value (2) then the formula will return the value from the result_vector (A2:A5) that corresponds to the last (bottom-most) value in the lookup_vector.

Here's how we get there...

We know that dividing a number by 0 will return the #DIV/0! error. As it turns out, the LOOKUP function will ignore these errors so we can leverage this behavior to our advantage.

When we divide the contents of the range A2:A5 we get:

A2 = 10 = 1/10 = 0.1
A3 = 20 = 1/20 = 0.05
A4 = 5 = 1/5 = 0.2
A5 = 0 = 1/0 = #DIV/0!

As I said, the #DIV/0! error is ignored.

Our lookup_value is 2 and the last value in the lookup_vector that is less than 2 is 0.2. 0.2 corresponds to cell A4. A4 contains 5. So:

=LOOKUP(2,1/A2:A5,A2:A5)

=5