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.

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

"jb" wrote:

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.

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))


--

Regards,

Peo Sjoblom






"Balan" wrote in message
...
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

"jb" wrote:

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.

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

"Peo Sjoblom" wrote:

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))


--

Regards,

Peo Sjoblom






"Balan" wrote in message
...
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

"jb" wrote:

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.

=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.

--
Biff
Microsoft Excel MVP


"Balan" wrote in message
...
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

"jb" wrote:

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.

Here's another one:

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

Format as DATE

--
Biff
Microsoft Excel MVP


"jb" wrote in message
...
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.

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

"T. Valko" wrote:

Here's another one:

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

Format as DATE

--
Biff
Microsoft Excel MVP


"jb" wrote in message
...
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.