VLookup Last Entry

J

jb

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

Balan

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
 
P

Peo Sjoblom

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
 
T

T. Valko

=SUMPRODUCT(MAX((A4:A33)*(B4:B33 said:
The formula has to be entered as an array formula
Click to expand...

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

jb

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

Balan

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
 
Top