如何用VLOOKUP与MATCH校验Sheet2名单归属并导出异常至Sheet3

这问题我碰到过好几次，用Excel的动态表格+函数就能完美解决，而且还能适配各工作表行列变动的情况，具体步骤和细节如下：

第一步：把Sheet1的数据转成动态表格（核心！适配行列变动） #

首先得让Sheet1的姓名-团队数据能自动适应新增/删除行列的变化，最好的方式就是转成Excel内置的「表格」：

• 选中Sheet1里的所有姓名和团队数据（如果没表头，建议先加个表头比如「姓名」「团队」，方便后续引用）
• 按下快捷键 Ctrl+T，弹出创建表对话框，勾选「我的表有标题」（加了表头的话），点击确定
• 给这个表格起个好记的名字，比如 TeamMemberList——在顶部「表格设计」选项卡的「表名称」框里修改就行

第二步：在Sheet2里实时校验（可选，快速查看异常） #

如果想在Sheet2里直接看到哪些姓名不符合要求，可以新增一列（比如B列），在B1单元格输入以下公式：

=IF(XLOOKUP(A1, TeamMemberList[姓名], TeamMemberList[团队], "")<>"Team B", "❌ 异常", "✅ 正常")

这个公式会自动查找当前姓名对应的团队：如果找不到姓名，或者团队不是Team B，就标记为异常，否则显示正常。下拉公式就能覆盖所有Sheet2里的姓名。

第三步：自动提取异常名单到Sheet3 #

接下来要把Sheet2里的异常姓名自动同步到Sheet3，分两种情况：

如果你用的是Excel 365/2021（支持动态数组） #

在Sheet3的A1单元格输入这个公式，异常名单会自动生成，而且后续Sheet2更新时会自动同步：

=FILTER(Sheet2!A:A, XLOOKUP(Sheet2!A:A, TeamMemberList[姓名], TeamMemberList[团队], "")<>"Team B", "✅ 无异常名单")

公式逻辑：用XLOOKUP匹配每个姓名的团队，筛选出团队不是Team B（或者找不到姓名）的记录，没异常的话会显示提示文本。

旧版Excel（不支持动态数组） #

如果你的Excel版本比较旧，用数组公式来实现，在Sheet3的A1单元格输入以下公式，然后按 Ctrl+Shift+Enter 确认（这是数组公式的专属确认方式）：

=IFERROR(INDEX(Sheet2!A:A, SMALL(IF(XLOOKUP(Sheet2!A:A, TeamMemberList[姓名], TeamMemberList[团队], "")<>"Team B", ROW(Sheet2!A:A), ""), ROW(A1))), "")

下拉这个公式直到出现空白单元格，就能列出所有异常姓名。

示例验证（对应你的测试数据） #

假设Sheet1的表格数据是：

Sheet2的A列是：

• Hendrix, Jimi
• Shepherd, Kenny
• Rose, Axl
• Keenan, Maynard

用上述公式后，Sheet3会自动输出：

• Rose, Axl
• Keenan, Maynard

完全符合预期的异常名单，而且不管后续Sheet1/Sheet2新增或删除行列，公式都会自动识别新的范围，不用手动调整单元格引用。

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