How to retrieve last date and amount from columns?

T

tfrentz

Hi,

I have a spreadsheet with dates and amounts in columns A and B. I want to
retrieve the last date and amount in both columns right before the empty
cells. The dates are in ascending order and there are spaces in some rows
and some of the amounts can be spaces or zero.

Here is an example

Column A Column B
.....other data here
....spaces here
01/01/2001 0
01/02/2001 5000
01/01/2002 10000
01/01/2003 20000
01/01/2004 25000
01/01/2005 0
....spaces here
10/27/2006 30000
10/28/2006 35000
....empty cells here

I want to retrieve the the last row which contains the date of 10/28/2006
and the amount of 35000 into two cells adjacent to each other just like they
are stored in column A and B of the table above.

Any help greatly appreciated.
 
R

Ragdyer

Enter this anywhere, in a cell formatted to a date format of your choice:

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

And then simply copy it across to the next column to get the last value in
Column B.
 
T

tfrentz

Hi,

Thanks, that worked, but I guess I didn't explain myself completely as my
intention was to be able to add data to the columns in the future. Can the
formula's range be modified to handle the whole column? That way I won't
have to manually adjust the ranges in the future. I tried making the range
A:A, which no success.

Also, could you explain what the parameters of the function mean?

thanks
 
F

Fred Smith

Vlookup will do what you want, as in:

=vlookup(999999999999,A:B,1) where 999999999 is greater than any value in
column A
=vlookup(999999999999,A:B,2) will pick up the adjacent column
 
T

tfrentz

Hi Fred,

That worked great. Thanks.

Fred Smith said:
Vlookup will do what you want, as in:

=vlookup(999999999999,A:B,1) where 999999999 is greater than any value in
column A
=vlookup(999999999999,A:B,2) will pick up the adjacent column
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top