Google Sheets中两个含重复值的不等长工作表数据交叉引用问题

前提约定 #

先统一两个工作表的命名规则，你可以根据自己的实际表名调整：

• 存储独立姓氏的工作表命名为「姓氏清单」，所有姓氏存放于该表A列（A1为表头，数据从A2开始）
• 主数据工作表命名为「数据总表」，姓名全名列存放于A列（A1为表头，数据从A2开始），匹配结果将生成在B列

实现公式 #

方案1：正则匹配（速度快，适合无特殊字符的姓氏场景） #

如果要返回TRUE/FALSE的布尔值，在B2单元格输入以下公式后下拉填充即可：

=SUMPRODUCT(--REGEXMATCH(A2, TEXTJOIN("|", TRUE, UNIQUE('姓氏清单'!A:A))))>0

如果要返回1/0的数值结果，公式调整为：

=SUMPRODUCT(--REGEXMATCH(A2, TEXTJOIN("|", TRUE, UNIQUE('姓氏清单'!A:A))))*1

如果希望整列自动生成结果不需要手动下拉，使用溢出公式：

=BYROW(A2:A, LAMBDA(full, IF(full="",,SUMPRODUCT(--REGEXMATCH(full, TEXTJOIN("|", TRUE, UNIQUE('姓氏清单'!A:A))))*1)))

方案2：普通字符串匹配（兼容性高，适合姓氏包含特殊符号的场景） #

如果姓氏里包含.、*、+等正则特殊字符，改用以下公式避免匹配错误，返回1/0结果：

=SUMPRODUCT(--ISNUMBER(SEARCH(UNIQUE('姓氏清单'!A:A), A2)))*1

公式说明 #

• UNIQUE('姓氏清单'!A:A)：自动去除姓氏列的重复值，避免重复判断拖慢性能
• TEXTJOIN("|", TRUE, ...)：把所有去重后的姓氏拼接成正则「或匹配」规则，符合多姓氏任意匹配的需求
• REGEXMATCH/SEARCH：两种不同的字符串匹配逻辑，按需选择即可
• SUMPRODUCT：对匹配结果求和，只要有一个姓氏命中就会返回≥1的结果，最终转换为你需要的1/0或者TRUE/FALSE

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