无需INDIRECT实现动态表格与列引用的Excel优化方案问询

First, let's break down why your original formula is causing performance issues: INDIRECT is a volatile function—it recalculates every time any cell in the workbook changes, not just when your input (A1) updates. This adds unnecessary computational overhead, especially if you're using this formula repeatedly.

Your attempt with an IF-based named range didn't work because the IF function returns a cell range array, not the actual Excel Table object—so you can't use the structured [column name] reference syntax with it. Here are two reliable, high-performance solutions:

Solution 1: Use CHOOSE for Direct Indexing (Best for Numeric Inputs) #

If A1 uses numeric values (1, 2, 3) to map to table1/table2/table3, this is the simplest approach:

1. Create a named range (Formulas > Define Name) called SelectedTable with this formula:

   =CHOOSE($A$1, table1, table2, table3)
   

   This directly returns the full Excel Table object corresponding to A1's value, not just a cell range.

2. Rewrite your SUMIFS formula to use the named range with structured references:

   =SUMIFS(SelectedTable[kpi_name], SelectedTable[filter1], UPPER($H13))
   

CHOOSE is non-volatile, so it only recalculates when A1 changes—this will drastically reduce unnecessary computations.

Solution 2: Use INDEX/MATCH or XLOOKUP for Custom Mapping #

If you need to keep your A2:B4 lookup table (e.g., A1 uses text labels instead of numbers), use this approach to return the Table object directly:

1. Update your lookup range: Make sure column B of A2:B4 references the actual Table objects, not text strings. For example, cell B2 should be =table1, B3 =table2, B4 =table3.

2. Create the SelectedTable named range with either of these formulas:

   • Using INDEX/MATCH (compatible with all Excel versions):

     =INDEX($B$2:$B$4, MATCH($A$1, $A$2:$A$4, 0))
     

   • Using XLOOKUP (simpler for newer Excel versions):

     =XLOOKUP($A$1, $A$2:$A$4, $B$2:$B$4)
     

3. Use the same SUMIFS formula as Solution 1:

   =SUMIFS(SelectedTable[kpi_name], SelectedTable[filter1], UPPER($H13))
   

Key Notes for Success #

• Ensure table1, table2, table3 are official Excel Tables (created via Insert > Table). This is critical because only Table objects support the structured [column name] syntax, and Excel optimizes Table calculations for performance.
• Avoid wrapping Table references in INDIRECT or converting them to text strings—this breaks the Table object reference and forces Excel to treat them as regular cell ranges.

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