Excel中按供应商优先级匹配保修索赔价格的XLOOKUP公式设置

业务场景 #

• 供职于跨国制造企业，负责保修索赔业务处理，需在Excel中搭建带优先级规则的查询表，自动匹配每笔保修索赔对应的供应商及零件结算价格
• 基础匹配逻辑：维修发生在欧盟区域时，优先匹配对应优先级的欧盟供应商报价，无匹配结果时顺延取用后续优先级供应商报价结算；亚洲区域维修遵循相同的顺次匹配规则

现有公式逻辑 #

目前已实现基于XLOOKUP的零件号+供应商双条件精确查询，匹配成功返回对应单价，无匹配返回"N/A"，现有公式如下：

=XLOOKUP(H2&E2;'Part Number List'!G:G&'Part Number List'!I:I;'Part Number List'!Q:Q;"N/A";0;"N/A")

公式参数说明：XLOOKUP(当前行零件号 & 当前行供应商; 价格对照表零件号列 & 价格对照表供应商列; 价格对照表单价列; 精确匹配; 无匹配返回值)

待落地优先级规则 #

需要实现按区域优先级自动顺延查询下一级供应商报价的功能，各区域供应商优先级如下：

• 维修地为欧盟（EU）：PRIO1供应商DE2FP，PRIO2供应商C2CUL，PRIO3无匹配返回手动查找提示
• 维修地为亚洲（Asia）：PRIO1供应商CNH23，PRIO2供应商DE2FP，PRIO3供应商CN342，全部无匹配返回手动查找提示

可直接复用的嵌套公式 #

假设表格中维修地区标识存储在F列（可根据实际表格列位置调整参数），使用IFERROR嵌套XLOOKUP实现顺次查询，公式如下：

=IFS(
    F2="EU",
    IFERROR(XLOOKUP(H2&"DE2FP";'Part Number List'!G:G&'Part Number List'!I:I;'Part Number List'!Q:Q;NA();0),
    IFERROR(XLOOKUP(H2&"C2CUL";'Part Number List'!G:G&'Part Number List'!I:I;'Part Number List'!Q:Q;NA();0),"N/A/需手动查找")),
    F2="Asia",
    IFERROR(XLOOKUP(H2&"CNH23";'Part Number List'!G:G&'Part Number List'!I:I;'Part Number List'!Q:Q;NA();0),
    IFERROR(XLOOKUP(H2&"DE2FP";'Part Number List'!G:G&'Part Number List'!I:I;'Part Number List'!Q:Q;NA();0),
    IFERROR(XLOOKUP(H2&"CN342";'Part Number List'!G:G&'Part Number List'!I:I;'Part Number List'!Q:Q;NA();0),"N/A/需手动查找"))),
    TRUE,"N/A/维修地未识别"
)

兼容提示：如果使用的Excel版本不支持IFS函数，可将外层IFS替换为多层嵌套IF实现相同判断逻辑；公式中涉及的列引用可根据实际表格结构调整。

公式运行逻辑 #

1. 首先识别当前索赔记录的维修地区，加载对应区域的供应商优先级队列
2. 从最高优先级供应商开始，匹配对应零件号的结算价格，匹配成功直接返回结果
3. 高优先级供应商无对应报价时，自动触发下一级供应商的价格查询
4. 所有优先级供应商均无匹配报价、或维修地无法识别时，返回对应提示信息

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