Blank cells and LOOKUP

[erickguz]

Hello.

I have a column with a cash balance. It fills up as data are input into a separate sheet, so although a balance may appear to only Row 100, the formula goes down to Row 1000.

From Rows 101 - 1000, I have the following "result" since there are still no data: " - ". This is the accounting format for "0", or zero dollars, which is what I need. I don't want to show a "0".

To get the last balance on the column, I use the following formula: =lookup(10^10,A:A).

The problem: I am getting a return of "-" with the lookup formula, since it returns the value in the last cell, Row 1000, that has a formula only.

How do I get my lookup formula to return the last actual balance, and not one of the results from the "empty" cells below it?

Thank you,
EG

[starguy]

Try this

=LOOKUP(2,1/(A:A>0),A:A)

[daddylonglegs]

=LOOKUP(2,1/(A:A>0),A:A)

I don't know if that formula will work in Excel 2007 but certainly in any other version of Excel you can't specify the whole column, it needs to be a specific range like

=LOOKUP(2,1/(A1:A1000>0),A1:A1000)