如何设置公式匹配policy#与Risk#并计算保费乘以折扣值？

前提说明 #

假设两个数据集的字段对应关系：

• Data#1：policy#（A列）、Risk#（B列）、Premium（C列）
• Data#2：Source（A列）、policy#（B列）、Risk#（C列）、Discount1（D列）、Discount2（E列）
  我们要在Data#2中新增列计算匹配后的乘积结果（以第2行数据为例）。

方案1：用XLOOKUP（支持Excel 365/2021、Google Sheets） #

XLOOKUP支持多条件匹配，直接通过拼接policy#和Risk#作为匹配键：

=XLOOKUP($B2&$C2, Data#1!$A:$A&Data#1!$B:$B, Data#1!$C:$C) * $D2 * $E2

• 把公式下拉应用到所有Data#2的行即可
• 如果匹配不到对应项，公式会返回#N/A，可以用IFERROR包裹处理：

  =IFERROR(XLOOKUP($B2&$C2, Data#1!$A:$A&Data#1!$B:$B, Data#1!$C:$C) * $D2 * $E2, 0)
  

方案2：用INDEX+MATCH（兼容所有Excel版本、Google Sheets） #

如果你的Excel版本不支持XLOOKUP，用经典的INDEX+MATCH组合实现双条件匹配：

=INDEX(Data#1!$C:$C, MATCH($B2&$C2, Data#1!$A:$A&Data#1!$B:$B, 0)) * $D2 * $E2

• 注意：在Excel 2019及更早版本中，这是数组公式，输入后需要按Ctrl+Shift+Enter完成输入（Excel 365/2021及Google Sheets无需此操作）
• 同样可以用IFERROR处理无匹配的情况：

  =IFERROR(INDEX(Data#1!$C:$C, MATCH($B2&$C2, Data#1!$A:$A&Data#1!$B:$B, 0)) * $D2 * $E2, 0)
  

关键注意点 #

• 必须保证两个数据集中的policy#和Risk#格式完全一致（比如都是文本类型、没有前后空格、数字格式统一），否则会出现匹配失败的情况
• 如果存在同一个policy#+Risk#对应多行Data#1的情况，上述公式只会返回第一个匹配到的Premium值；若需要汇总这类情况的Premium，可以用SUMIFS替代INDEX：

  =SUMIFS(Data#1!$C:$C, Data#1!$A:$A, $B2, Data#1!$B:$B, $C2) * $D2 * $E2
  

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