Google Sheets中基于三列与指定月份匹配预算数据的公式需求

问题说明 #

• 现有两个表格：
  • Table1：当月预算表，A6:C12为手动录入的「Budget No」「Company」「Department」数据
  • Table2：全月预算表，A16:M22为各月份预算数据，其中存在「Sum」行，对应公司各部门共享的预算（无单独部门预算）
• 需求：在Table1的D6:D12自动填充单元格B2指定月份的预算值，匹配优先级为：先匹配「Budget No+Company+Department」，匹配失败则匹配「Budget No+Company+Sum」

可行公式 #

方案1：使用XLOOKUP嵌套（更直观） #

在D6单元格输入以下公式，下拉填充至D12：

=XLOOKUP(
  A6&B6&C6,
  Table2!$A$16:$A$22&Table2!$B$16:$B$22&Table2!$C$16:$C$22,
  INDEX(Table2!$D$16:$M$22,,MATCH($B$2,Table2!$D$15:$M$15,0)),
  XLOOKUP(
    A6&B6&"Sum",
    Table2!$A$16:$A$22&Table2!$B$16:$B$22&Table2!$C$16:$C$22,
    INDEX(Table2!$D$16:$M$22,,MATCH($B$2,Table2!$D$15:$M$15,0)),
    ""
  )
)

方案2：使用INDEX+MATCH+IFERROR（兼容性更强） #

同样在D6单元格输入，下拉填充：

=IFERROR(
  INDEX(Table2!$D$16:$M$22,
    MATCH(A6&B6&C6, Table2!$A$16:$A$22&Table2!$B$16:$B$22&Table2!$C$16:$C$22, 0),
    MATCH($B$2, Table2!$D$15:$M$15, 0)
  ),
  INDEX(Table2!$D$16:$M$22,
    MATCH(A6&B6&"Sum", Table2!$A$16:$A$22&Table2!$B$16:$B$22&Table2!$C$16:$C$22, 0),
    MATCH($B$2, Table2!$D$15:$M$15, 0)
  )
)

公式解释 #

• MATCH($B$2, Table2!$D$15:$M$15, 0)：定位B2指定的月份在Table2表头（D15:M15）中的列位置
• 外层逻辑：先尝试精确匹配「Budget No+Company+Department」的组合，获取对应月份预算；如果匹配不到（即该部门无单独预算），则自动匹配同Budget No、同Company且Department为「Sum」的行，提取共享预算
• 最终的""用于处理完全匹配失败的情况，可根据需求替换为0或其他提示文本

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