Substracting a value, that is in a previous cell from the value found with lookup(9^9...

Hi,

I got a row of numbers, where every week a new value gets added. To display the changes from the previous week I use

=LOOKUP(9^9;C3:O3)-F3

where the LOOKUP picks the last cell with a value and "F3" is the reference to the previous weeks value, which I have to adjust each time. And that is exactly, what I don't want to have to do anymore.

How could I replace the "F3" with a formula, that would automatically pick kind of "last cell with a value" (what I get with the LOOKUP) minus one column?

Thanks
xingo

please post sample file

=LOOKUP(9^9,A4:M4)-INDEX(A4:M4,LOOKUP(9^9,A4:M4,COLUMN(A4:M4)-1))
Try this