VLookup Last Entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jb - 24 Sep 2007 02:50 GMT

Hi, I am trying to use VLookup to find the last date that a value was entered
in. I then use this date in a different location to calculate MTD Goals. My
spreadsheet looks like the following:

Date Total
9/1 $5312.44
9/2 $3419.67
9/3 $1619.17
9/4
9/5
9/6
9/7
to 9/30

This a standard format that is submitted to me with the data. So I would
like to be able to return the date of 9/3 since it is the last day with a
value. Any help would be greatly appreciated as I would then be able to
automate my report without have to enter in the last date of data available.
Thanks for any help.

Reply to this Message

Balan - 24 Sep 2007 03:12 GMT

jb
You may try
=SUMPRODUCT(MAX((A4:A33)*(B4:B33<>0)))
where A4:A33 contain the dates and B4:B33, the values for the dates. The
formula has to be entered as an array formula i.e., by pressing
Ctrl+Shift+Enter and not merely by Enter. Excel will automatically place
curly brackets around the function. (Curly brackets should not be put by you)

Best Wishes

Balan

> Hi, I am trying to use VLookup to find the last date that a value was entered
> in. I then use this date in a different location to calculate MTD Goals. My
[quoted text clipped - 15 lines]
> automate my report without have to enter in the last date of data available.
> Thanks for any help.

Reply to this Message

Peo Sjoblom - 24 Sep 2007 03:18 GMT

You might as well use the shorter SUM if you are going to array enter the
formula but since it is SUMPRODUCT you don't need to array enter it. Another
way using array entering would be

=MAX(IF(B4:B33<>"",A4:A33))

or

=MAX((B4:B33<>"")*(A4:A33))

Signature

Regards,

Peo Sjoblom

> jb
> You may try
[quoted text clipped - 31 lines]
>> available.
>> Thanks for any help.

Reply to this Message

Balan - 24 Sep 2007 14:24 GMT

Mr.Peo and Mr.Valko
Many thanks for telling me that sumproduct need not be entered as an array
formula. A learner myself I shouldn't have ventured to make the suggestion
without verifying whether it works normally.

Balan

> You might as well use the shorter SUM if you are going to array enter the
> formula but since it is SUMPRODUCT you don't need to array enter it. Another
[quoted text clipped - 41 lines]
> >> available.
> >> Thanks for any help.

Reply to this Message

T. Valko - 24 Sep 2007 03:27 GMT

>=SUMPRODUCT(MAX((A4:A33)*(B4:B33<>0)))
>The formula has to be entered as an array formula

Not necessary to array enter in this case. Normally entered will work.

Signature

Biff
Microsoft Excel MVP

> jb
> You may try
[quoted text clipped - 31 lines]
>> available.
>> Thanks for any help.

Reply to this Message

T. Valko - 24 Sep 2007 03:25 GMT

Here's another one:

=LOOKUP(1E10,B2:B10,A2:A10)

Format as DATE

Signature

Biff
Microsoft Excel MVP

> Hi, I am trying to use VLookup to find the last date that a value was
> entered
[quoted text clipped - 18 lines]
> available.
> Thanks for any help.

Reply to this Message

jb - 24 Sep 2007 03:44 GMT

Thank you all for your quick responses. Can't wait to get to work tomorrow
and try them out.

> Here's another one:
>
[quoted text clipped - 24 lines]
> > available.
> > Thanks for any help.

Reply to this Message