基于两个文本条件的Excel Lookup函数公式设计咨询

嘿，针对你这个需要匹配地区（America/Australia等）和品牌（Toyota/Lexus等）两个文本条件、返回对应风险等级的5×5场景需求，我整理了几个实用的方案，你可以根据自己的使用习惯和Excel版本来选：

方案1：创建辅助Lookup表（最推荐，易维护） #

这是我最建议的方式，后续修改或调整规则超级方便，不用动公式：

1. 先在Excel的空白区域（比如D1:H6）做一个二维对照表：
   • D列（行标题）：依次输入America、Australia、Europe、Asia、Africa
   • E1:H1（列标题）：依次输入Toyota、Lexus、Ford、Audi、BMW
   • 对应单元格（比如E2）填入对应的风险等级（比如Negligible），把25个组合的结果都填好
2. 假设你的地区值存放在A1单元格，品牌值存放在B1单元格，那么公式可以写：

   =INDEX($E$2:$H$6, MATCH($A$1, $D$2:$D$6, 0), MATCH($B$1, $E$1:$H$1, 0))
   

   • 公式逻辑：MATCH($A$1, $D$2:$D$6, 0)找到地区对应的行号，MATCH($B$1, $E$1:$H$1, 0)找到品牌对应的列号，INDEX取出交叉位置的风险等级
   • 注意用绝对引用（$符号），这样复制公式到其他单元格时，对照表的范围不会偏移

方案2：SWITCH函数嵌套（无需辅助表，直接写公式） #

如果不想额外做对照表，可以用嵌套的SWITCH函数直接实现，适合Excel 2019及以上版本：

=SWITCH($A$1,
  "America", SWITCH($B$1, "Toyota", "Negligible", "Lexus", "Low", "Ford", "Medium", "Audi", "High", "BMW", "Critical"),
  "Australia", SWITCH($B$1, "Toyota", "Low", "Lexus", "Medium", "Ford", "High", "Audi", "Critical", "BMW", "Negligible"),
  "Europe", SWITCH($B$1, "Toyota", "Medium", "Lexus", "High", "Ford", "Critical", "Audi", "Negligible", "BMW", "Critical"),
  "Asia", SWITCH($B$1, "Toyota", "High", "Lexus", "Critical", "Ford", "Negligible", "Audi", "Low", "BMW", "Medium"),
  "Africa", SWITCH($B$1, "Toyota", "Critical", "Lexus", "Negligible", "Ford", "Low", "Audi", "Medium", "BMW", "High"),
  "无效地区"
)

• 逻辑：先匹配A1的地区，再在对应地区的分支里匹配B1的品牌，返回对应的风险等级
• 缺点：25个条件写起来有点繁琐，后续修改规则需要直接编辑公式，不如辅助表直观

方案3：XLOOKUP+文本拼接（适合Excel 365/2021版本） #

如果你用的是新版本Excel（支持动态数组），可以把两个条件拼接成唯一键，用XLOOKUP快速匹配：

方式A：配合辅助列 #

1. 在空白列（比如D列）拼接地区和品牌，比如D2单元格输入=A2&"|"&B2（用|作为分隔符避免歧义）
2. E列对应填入风险等级
3. 查找公式：

   =XLOOKUP($A$1&"|"&$B$1, $D$2:$D$26, $E$2:$E$26, "无匹配组合")
   

方式B：直接用数组常量（无需辅助列） #

=XLOOKUP($A$1&"|"&$B$1,
  {"America|Toyota","America|Lexus","America|Ford","America|Audi","America|BMW",
   "Australia|Toyota","Australia|Lexus","Australia|Ford","Australia|Audi","Australia|BMW",
   "Europe|Toyota","Europe|Lexus","Europe|Ford","Europe|Audi","Europe|BMW",
   "Asia|Toyota","Asia|Lexus","Asia|Ford","Asia|Audi","Asia|BMW",
   "Africa|Toyota","Africa|Lexus","Africa|Ford","Africa|Audi","Africa|BMW"},
  {"Negligible","Low","Medium","High","Critical",
   "Low","Medium","High","Critical","Negligible",
   "Medium","High","Critical","Negligible","Critical",
   "High","Critical","Negligible","Low","Medium",
   "Critical","Negligible","Low","Medium","High"},
  "无效组合"
)

• 逻辑：把两个条件合并成一个唯一字符串，XLOOKUP直接匹配对应的风险等级
• 优缺点：不用辅助表，但数组常量写起来很长，修改规则需要调整数组内容

额外提示 #

• 为了避免输入错误导致公式匹配失败，可以给A1和B1单元格设置数据验证，做成下拉菜单选择地区和品牌，确保输入值和对照表完全一致
• 如果需要处理空值或错误，可以在公式外层套IFERROR，比如=IFERROR(INDEX(...), "无匹配")

备注：内容来源于stack exchange，提问作者AlexK