如何在筛选后的数据上使用VLOOKUP实现跨Excel文件数据匹配

需求说明 #

需在Excel 2的工作表中，基于Excel 1的特定列数据做筛选：当Excel 2的Col1值在Excel 1的Col1中存在，且Excel 1对应行的Col2为「Active」时，在Excel 2的Col2填入「Eligible」，否则留空。

示例数据 #

Excel 1 数据源

Col1     Col2
ID223    Active
ID225    Not Active
ID667    Static
ID345    Active
ID678    Active

Excel 2 原始数据

Col1     Col2
ID223    
ID225    
ID623    
ID345    
ID678    

期望输出（Excel 2）

Col1     Col2
ID223    Eligible
ID225    
ID623    
ID345    Eligible
ID678    Eligible

已尝试的公式 #

• 公式1：=VLOOKUP(A2, [Excel 2.XLSX]Sheet1!$A:$B,1,0)
• 公式2：=IF(ISNA(VLOOKUP(A2, [Excel 2.XLSX]Sheet1!$A:$B,1,FALSE)),"", "Eligible")

问题修正与正确公式 #

之前的公式存在两处问题：一是文件引用错误（应指向存储状态数据的Excel 1而非Excel 2）；二是仅验证了ID是否存在，未检查对应行的「Active」状态。

适用所有Excel版本的公式（Excel 2的B2单元格，下拉填充） #

=IF(AND(NOT(ISNA(VLOOKUP(A2, [Excel 1.XLSX]Sheet1!$A:$B,2,FALSE))), VLOOKUP(A2, [Excel 1.XLSX]Sheet1!$A:$B,2,FALSE)="Active"), "Eligible", "")

新版Excel简化公式（XLOOKUP更直观） #

=IF(XLOOKUP(A2, [Excel 1.XLSX]Sheet1!$A:$A, [Excel 1.XLSX]Sheet1!$B:$B, "")="Active", "Eligible", "")

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