VBA基于动态变量编写跨表INDEX/MATCH数组公式返回0值问题

• 公式生成时自动插入的@隐式交集运算符是核心问题。通过普通.Formula属性写入双条件数组匹配逻辑时，Excel默认不会识别逐行遍历的数组运算规则，会自动在源数据范围前添加@，把原本需要全范围逐元素比对的逻辑，变成只取和当前公式单元格同位置的单个源值做匹配，完全无法命中真实匹配行，最终只会触发IFERROR的兜底返回值0。
• 原有公式的引用缺少必要的绝对锁配置，若后续批量向右/向下填充公式，会出现匹配范围偏移、活动名称引用错位的问题。

• 替换数组公式的写入方式：对支持动态数组的Excel版本（365/2021及以后），把c.Formula = f改成c.Formula2 = f，这一写入方式会自动识别数组运算逻辑，不会插入多余的@标记；对更早的旧版本Excel，改用c.FormulaArray = f写入CSE数组公式，同样可以避免隐式交集问题。
• 修正公式内的引用规则，匹配实际业务逻辑：
  • 活动名称匹配值要做成行绝对、列相对的格式，确保公式横向填充时，能自动取当前列第2行的活动名称，不会锁死某一列的固定值
  • 所有源数据的匹配范围、返回范围都加上列绝对锁，确保公式批量填充时，源表的匹配范围不会发生偏移

Dim ws As Worksheet, lastRow As Long, c As Range
Dim f As String

Set ws = Worksheets("Campaign Report")

' 动态取三列的最大行，避免新增数据后范围截断
lastRow = Application.Max(ws.Range("Q100000").End(xlUp).Row, _
                       ws.Range("A100000").End(xlUp).Row, _
                       ws.Range("F100000").End(xlUp).Row)

Set c = ActiveCell

' 公式逻辑调整：用R1C1样式引用当前列第2行的活动名，自动适配横向填充
f = "=IFERROR(INDEX(<addrQ>, MATCH(1,(A<rw>=<addrA>)*(R2C[0]=<addrF>),0)),0)"
f = Replace(f, "<rw>", c.Row)
' 源表范围全部加列绝对锁，避免填充时范围偏移
f = Replace(f, "<addrA>", "'" & ws.Name & "'!$A$2:$A$" & lastRow)
f = Replace(f, "<addrF>", "'" & ws.Name & "'!$F$2:$F$" & lastRow)
f = Replace(f, "<addrQ>", "'" & ws.Name & "'!$Q$2:$Q$" & lastRow)

' 写入数组公式，适配动态数组版本Excel
c.Formula2 = f
' 若使用2019及更早版本Excel，将上一行替换为：
' c.FormulaArray = f

' 光标右移，支持连续批量生成公式
Set c = c.Offset(0, 1)

=IFERROR(INDEX('Campaign Report'!$Q$2:$Q$163,MATCH(1,(A146='Campaign Report'!$A$2:$A$163)*(G$2='Campaign Report'!$F$2:$F$163),0)),0)

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