能否同时在多列使用=ISNUMBER(SEARCH(函数？企业国别信息校验

需求概述 #

• 工作表Master中，逐行校验Organization Name（E列）、Work Country（AV列）、**Work Location（AW列）**三列是否对应同一国家
• 示例数据（前6行正确，后3行错误）：

校验规则 #

• 核心逻辑：国家对应指定国别代码（如Austria对应at）
• 若Work Location为Expatriate，直接判定为正确
• 校验公式需写在Mismatch Finder工作表中
• 部门代码CP、IS、ES与西班牙国别代码es重合，需搜索"CP ES"和"S ES"来匹配西班牙
• 部分国家多代码映射：

当前问题 #

自行编写的校验公式因涉及60个国家，字符数超限无法使用，原公式如下：

=IF(OR( AND( ISNUMBER(SEARCH(" at ",Table2[@[Work Location]])), ISNUMBER(SEARCH("Austria",Table2[@[Work Country]])), ISNUMBER(SEARCH(" at ",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" be ",Table2[@[Work Location]])), ISNUMBER(SEARCH("Belgium",Table2[@[Work Country]])), ISNUMBER(SEARCH(" be ",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" es ",Table2[@[Work Location]])), ISNUMBER(SEARCH("Spain",Table2[@[Work Country]])), ISNUMBER(SEARCH("cp es",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" es ",Table2[@[Work Location]])), ISNUMBER(SEARCH("Spain",Table2[@[Work Country]])), ISNUMBER(SEARCH("s es",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" cs ",Table2[@[Work Location]])), ISNUMBER(SEARCH("Serbia",Table2[@[Work Country]])), ISNUMBER(SEARCH(" rs ",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" uk ",Table2[@[Work Location]])), ISNUMBER(SEARCH("United Kingdom",Table2[@[Work Country]])), ISNUMBER(SEARCH(" gm ",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" gb ",Table2[@[Work Location]])), ISNUMBER(SEARCH("United Kingdom",Table2[@[Work Country]])), ISNUMBER(SEARCH(" gm ",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" uk ",Table2[@[Work Location]])), ISNUMBER(SEARCH("United Kingdom",Table2[@[Work Country]])), ISNUMBER(SEARCH(" gb ",Table2[@[Organization Name]]))), AND( ISNUMBER(SEARCH(" gb ",Table2[@[Work Location]])), ISNUMBER(SEARCH("United Kingdom",Table2[@[Work Country]])), ISNUMBER(SEARCH(" gb ",Table2[@[Organization Name]]))), ISNUMBER(SEARCH("Expatriate",Master[@[Work Location]])))),     "correct","incorrect")

未接触过Lambda等高级函数，需要简易易懂的解决方案。

解决方案（无需高级函数） #

步骤1：创建国家代码映射表 #

新建一个工作表（命名为CountryMap），整理所有国家的完整映射关系，每行对应一个国家的一组匹配规则（多代码拆分成多行）：

注：把60个国家的所有代码组合都拆成单独行，确保每个Org代码和Work Loc代码的组合都对应正确国家。

步骤2：在Mismatch Finder中编写校验公式 #

假设Mismatch Finder的B2单元格对应Master的第2行，输入以下公式（根据实际列调整引用）：

=IF(ISNUMBER(SEARCH("Expatriate",Master!AW2)),"correct",
    IF(COUNTIFS(CountryMap!$A:$A,Master!AV2,
                CountryMap!$B:$B,"*"&Master!E2&"*",
                CountryMap!$C:$C,"*"&Master!AW2&"*")>0,
        "correct","incorrect"))

公式解释：

1. 优先判断Work Location（AW列）是否为Expatriate，是则返回correct
2. 否则用COUNTIFS在映射表中匹配：
   • 国家名称匹配Work Country（AV列）
   • Org_Name_Code包含在Organization Name（E列）中
   • Work_Loc_Code包含在Work Location（AW列）中
3. 找到匹配项返回correct，无匹配则返回incorrect

步骤3：批量应用公式 #

选中B2单元格，下拉填充到所有需要校验的行即可。

优势 #

• 用映射表管理规则，避免超长公式，易维护
• 仅使用基础Excel函数，无需掌握高级功能
• 新增/修改国家规则只需调整映射表，无需修改公式

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