Hi
instead of
VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance
!$A$4:$E$10000,2,FALSE)
try using the followung array formula (entered with CTRL+SHIFT+ENTER):


INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Perfo rmance!$A$4:$A$10000<
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$
A$10000)),Performance!$A$4:$A$10000,0))


--
Regards
Frank Kabel
Frankfurt, Germany

"Ted Metro" schrieb im Newsbeitrag
...
I have one table of balance information and I track the balance
daily.

Then I have a summary table that pulls growth numbers from that table
for 1
week, 1 month, and 3 months.

The formula in my summary table for 1 month is -


=(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE
AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,
2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)
),Performance!$A$4:$E$10000,2,FALSE)))


Here's my problem -

I only enter these numbers during the week. So on December 13th my
table
looks back 1 month and errors out because Nomveber 13th was a
Saturday and I
didn't enter a number. In this case I need the formula to go to the
last
balance entered which would have been November 12th's Friday balance.
Is
there a way to modify my formula so that if the date doesn't exist in
the
table then it will go to the most recent previous entry -- If one
month ago
(or three months ago) is a Saturday or Sunday it calculates off of
the Friday
balance?

I guess I could always enter weekend days in my table and just copy
and
paste over the data from Friday, but I hope there's an easier way so
that I
don't have to add meaningless records to my table.

Ted