VBA Index table by date offset.

[u/WontonCardboard]

Attempting to make a function that will Index lookup by specified date offset but I keep running into Value error. My employees are to add new rows to table each day to do data entry. I need this function in order to scrape specific rolling date windows for averages. t

Public Function DEXDOFF(array1 As ListObjects, arraydc As ListObjects, offset As Double, column As Double) As Double

'Function to index table by specified number of days offeset from present

DEXDOFF = Application.WorksheetFunction.Index(array1, Application.WorksheetFunction.Match(offset, arraydc, 0), column)

End Function

Typically input looks like =DEXDOFF('Table1[#All],'Table1[[#All],[Date]],(Today()-Day(offset),'column')

[u/AutoModerator]

/u/WontonCardboard - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/terminate18]

Option Explicit

Public Function DEXDOFF(tbl As Range, dateCol As Range, daysBack As Long, colNum As Long) As Variant
    Dim r As Variant

    Application.Volatile

    r = Application.Match(CLng(Date) - daysBack, dateCol, 0)

    If IsError(r) Then
        DEXDOFF = CVErr(xlErrNA)
    Else
        DEXDOFF = Application.Index(tbl, r, colNum)
    End If
End Function

Honestly though, if you’re after rolling averages you don’t need VBA at all:

=AVERAGEIFS(Table1[Amount], Table1[Date], ">="&TODAY()-7, Table1[Date], "<"&TODAY())

That gives you the last 7 days (not counting today), and the file can stay a normal xlsx. For a single lookup, =XLOOKUP(TODAY()-7, Table1[Date], Table1[Amount], "") does the same thing as the UDF.

[u/WontonCardboard → u/terminate18]

Solution Verified

[u/reputatorbot → u/WontonCardboard]

You have awarded 1 point to terminate18.

---

^{I am a bot - please contact the mods with any questions}

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AVERAGEIFS	Excel 2007+: Returns the average (arithmetic mean) of all cells that meet multiple criteria.
TODAY	Returns the serial number of today's date
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #49551 for this sub, first seen 20th Oct 2026, 20:12] [FAQ] [Full list] [Contact] [Source code]}