"snax500" wrote:
I want to have a lookup formula that looks up all dates before the
weekending date.

I'm not sure what it is you're after: do you want to be told the week ending
date for a given date, or do you want a list of all the dates for the week
that ends on a given date? If you really want a lookup, what's it supposed
to do?

In the first case,

Public Function WeekendingDate(day As Date) As Date
'could use some parameter checking... day < 0, etc.
Dim daynumber As Integer
daynumber = Weekday(day)
WeekendingDate = day + 7 - daynumber
End Function

In the second case, just use date arithmetic: day - 1, day - 2, etc.

Or were you after something else?