Excel数组公式自适应列与行数改造问题求助

Hey there! Let's break down how to fix your dynamic array formula issue step by step. First, I'll recap what your original formula does, then point out why your first rewrite failed, and finally give you a few solid solutions—including a super clean one that avoids VBA entirely.

What Your Original Formula Does #

Your initial array formula:

=IFERROR(INDEX(Tasks!$H$2:$H$65536;SMALL(IF(A2=Tasks!$A$2:$A$65536;ROW(Tasks!$A$2:$A$65536)-ROW(Tasks!$A$2)+1);ROW($1:$1)));"")

is meant to pull the Nth matching result from the Owned By column (originally H) in the Tasks sheet, based on the ID in cell A2. The problem is you have to manually update column letters and row ranges when your data shifts each month.

Why Your Rewrite Failed #

You tried to build a range address as a text string (like "Tasks!$H$2:$H$100"), but the INDEX function needs an actual cell range object—not just text. That's why your formula didn't work. You need to use the INDIRECT function to convert that text string into a valid range reference.

Solution 1: Using Your Existing VBA Col_Letter Function #

Let's keep your VBA function but fix the formula with INDIRECT. Here's the corrected array formula (remember to press Ctrl+Shift+Enter to confirm it if you're on an older Excel version; Excel 365/2021 can just press Enter):

=IFERROR(INDEX(
    INDIRECT("Tasks!" & "$" & Col_Letter(COLUMN(Table32[#Headers].[Owned By])) & "$2:$" & Col_Letter(COLUMN(Table32[#Headers].[Owned By])) & "$" & ROW(Table32[#Data])+ROWS(Table32)-1),
    SMALL(
        IF(
            A2=INDIRECT("Tasks!" & "$" & Col_Letter(COLUMN(Table32[#Headers].[ID])) & "$2:$" & Col_Letter(COLUMN(Table32[#Headers].[ID])) & "$" & ROW(Table32[#Data])+ROWS(Table32)-1),
            ROW(INDIRECT("Tasks!" & "$" & Col_Letter(COLUMN(Table32[#Headers].[ID])) & "$2:$" & Col_Letter(COLUMN(Table32[#Headers].[ID])) & "$" & ROW(Table32[#Data])+ROWS(Table32)-1)) - ROW(Table32[#Data]) + 1
        ),
        ROW($1:$1)
    )
), "")

Key Fixes Here: #

• Wrapped your text-based range addresses in INDIRECT() to turn them into valid cell ranges.
• Used Table32[#Data] to get the first row of your data and ROWS(Table32) to get the total number of rows—so the range automatically updates when you add/remove rows.
• Simplified the row offset calculation to use the table's data row as the base, instead of a fixed cell reference.

Solution 2: No VBA Needed (Recommended!) #

If you want to avoid relying on macros (since some environments disable them), you can replace your Col_Letter function with built-in Excel functions. Use SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[ID]), 4), "1", "") to get the column letter without VBA.

Here's the full formula:

=IFERROR(INDEX(
    INDIRECT("Tasks!" & SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[Owned By]), 4), "1", "") & "2:" & SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[Owned By]), 4), "1", "") & ROW(Table32[#Data])+ROWS(Table32)-1),
    SMALL(
        IF(
            A2=INDIRECT("Tasks!" & SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[ID]), 4), "1", "") & "2:" & SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[ID]), 4), "1", "") & ROW(Table32[#Data])+ROWS(Table32)-1),
            ROW(INDIRECT("Tasks!" & SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[ID]), 4), "1", "") & "2:" & SUBSTITUTE(ADDRESS(1, COLUMN(Table32[#Headers].[ID]), 4), "1", "") & ROW(Table32[#Data])+ROWS(Table32)-1)) - ROW(Table32[#Data]) + 1
        ),
        ROW($1:$1)
    )
), "")

Even Better: Use Excel Table References Directly #

Wait a second—you're already using Excel Tables (Table32, Table8)! You can skip all the address拼接 entirely and use structured table references. This is the cleanest, most dynamic solution:

=IFERROR(INDEX(
    Tasks[Owned By],
    SMALL(
        IF(A2=Tasks[ID], ROW(Tasks[ID])-ROW(Tasks[#Headers])),
        ROW($1:$1)
    )
), "")

Why This Is the Best Option: #

• Auto-adjusts columns: If you move the ID or Owned By columns in the Tasks table, Tasks[ID] and Tasks[Owned By] will automatically point to the right columns.
• Auto-adjusts rows: When you add or delete rows in the Tasks table, the range updates automatically—no manual tweaks needed.
• Simpler formula: No more messy string concatenation or INDIRECT calls, which makes the formula easier to read and less error-prone.

内容的提问来源于stack exchange，提问作者mabanger