如何用IF与VLOOKUP将移民状态值分为Y、N、Unknown三类？

核心思路 #

先通过单次VLOOKUP获取原始移民状态值，再用分类映射函数直接返回Y/N/Unknown，彻底避免重复调用VLOOKUP的冗余操作。

方案1：Excel 365/2021及以上版本（用SWITCH，最简洁直观） #

假设你的基础VLOOKUP公式为VLOOKUP(lookup_value, table_array, col_index, FALSE)，将其作为SWITCH的判断源，按规则映射结果：

=SWITCH(
    VLOOKUP(lookup_value, table_array, col_index, FALSE),
    "UK National", "Y",
    "Irish National", "Y",
    "EEA national – settled status", "Y",
    "ILtR", "Y",
    "Refugee", "Y",
    "Other limited leave without NRPF", "Y",
    "Refused Asylum seeker", "N",
    "No valid leave/undocumented", "N",
    "EEA – no status", "N",
    "Unknown"  # 所有未匹配的情况（含指定Unknown类、空单元格）统一返回Unknown
)

优化空值处理

如果VLOOKUP可能返回空单元格，可通过TRIM+空文本转换明确匹配空值：

=SWITCH(
    TRIM(VLOOKUP(lookup_value, table_array, col_index, FALSE)&""),
    "", "Unknown",
    "UK National", "Y",
    "Irish National", "Y",
    "EEA national – settled status", "Y",
    "ILtR", "Y",
    "Refugee", "Y",
    "Other limited leave without NRPF", "Y",
    "Refused Asylum seeker", "N",
    "No valid leave/undocumented", "N",
    "EEA – no status", "N",
    "Unknown"
)

方案2：兼容旧版Excel（用LOOKUP+映射表） #

先在任意空白区域（比如Sheet2的A:B列）建立分类映射表：

然后用以下公式，单次调用VLOOKUP后通过LOOKUP匹配映射表：

=IFERROR(
    LOOKUP(
        VLOOKUP(lookup_value, table_array, col_index, FALSE),
        Sheet2!$A$2:$A$13,
        Sheet2!$B$2:$B$13
    ),
    "Unknown"
)

方案优势 #

• 仅执行一次VLOOKUP，降低计算开销，公式结构更简洁
• 分类规则可集中维护（尤其方案2的映射表），后续修改状态分类只需调整映射项，无需改动主公式

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