双条件匹配验证：Excel中判断员工指定日期是否在岗

一、Excel公式实现 #

1. COUNTIFS函数（推荐，简洁高效） #

假设Sheet2中：

• A列为待验证日期（如A3为22-August-22）
• B列为待验证员工姓名（如B3为David）
• Sheet1中日期列是A列，员工姓名列是D列（可根据你的实际列位置调整）

在Sheet2的C3单元格输入以下公式，下拉填充即可批量验证：

=COUNTIFS(Sheet1!$A:$A, Sheet2!A3, Sheet1!$D:$D, Sheet2!B3) > 0

逻辑：COUNTIFS统计Sheet1中同时满足「日期匹配A3」且「姓名匹配B3」的行数，若行数大于0则返回TRUE，否则返回FALSE。

2. SUMPRODUCT函数（兼容旧版Excel） #

若你的Excel版本不支持COUNTIFS（如Excel 2007及更早），可使用此公式：

=SUMPRODUCT(--(Sheet1!$A$1:$A$2000=Sheet2!A3), --(Sheet1!$D$1:$D$2000=Sheet2!B3)) > 0

逻辑：--将逻辑判断结果转换为1/0，SUMPRODUCT计算两个条件数组的乘积和，结果大于0则说明存在匹配行。

3. XLOOKUP函数（Excel 365/2021及以上） #

=NOT(ISERROR(XLOOKUP(1, (Sheet1!$A$1:$A$2000=Sheet2!A3)*(Sheet1!$D$1:$D$2000=Sheet2!B3), Sheet1!$A$1:$A$2000)))

逻辑：通过数组条件匹配同时满足日期和姓名的行，XLOOKUP找到匹配则返回对应值，否则报错，NOT(ISERROR())将结果转换为TRUE/FALSE。

二、原有公式失效原因 #

你尝试的AND(VLOOKUP(...), VLOOKUP(...))仅分别验证「日期是否存在于Sheet1日期列」和「姓名是否存在于Sheet1姓名列」，但未检查这两个条件是否出现在同一行，因此会出现误判（比如日期和姓名分别存在但不在同一条记录时，也会返回TRUE）。

三、VBA代码实现（批量自动处理） #

若需要更自动化的批量验证，可使用以下宏：

Sub CheckEmployeeStatus()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, j As Long
    Dim matchFound As Boolean
    
    ' 指定工作表
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' 获取数据最后一行
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Sheet2待验证行（假设第1行是表头）
    For i = 2 To lastRow2
        matchFound = False
        ' 遍历Sheet1查找匹配
        For j = 2 To lastRow1
            If ws1.Cells(j, "A").Value = ws2.Cells(i, "A").Value And _
               ws1.Cells(j, "D").Value = ws2.Cells(i, "B").Value Then
                matchFound = True
                Exit For
            End If
        Next j
        ' 写入验证结果到C列
        ws2.Cells(i, "C").Value = matchFound
    Next i
    
    MsgBox "批量验证完成！"
End Sub

使用步骤：

1. 按Alt+F11打开VBA编辑器
2. 插入模块，粘贴上述代码
3. 运行宏即可自动填充验证结果

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