I am stumped. Help with Formula...

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
....

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)
 
In A1 enter the formula

=LOOKUP(10^10,D:D)
OR

=LOOKUP(10^10,D$1:D$100)


If this post helps click Yes
 
I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
...

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)
Click to expand...

If your values are all numbers, then:

A1: =LOOKUP(9.9E+307,D:D)

If there might be non-numeric data, then:

A1: =LOOKUP(2,1/(D:D<>""),D:D)

Note that in versions of Excel prior to 2007, you may not be able to reference
an entire column. If that is the case, then substitute D1:D65535 for the D:D
references.
--ron
 
Ok, this =LOOKUP(10^10,D:D) worked excellent, but now I have a further
problem. I have to ultimately upload this spreadsheet to google docs for
others to look at, but google docs doesn't recognize this lookup formula.
Any more thoughts?
 
Thank you. I got some help over at Google Docs. The answer was ...
=ArrayFormula(FILTER( D:D ; ROW(D:D)=MAX(FILTER(ROW(D:D) ; ISNUMBER(D:D) ) )
))

Thank you everyone!
 
Back
Top