Excel多列匹配（空白视为匹配）及VBA/公式解决方案问询

VBA方案（优先推荐） #

以下代码实现Sheet1与Sheet2的匹配逻辑：Sheet2空白单元格视为匹配项，匹配成功返回对应J列内容，无匹配则留空。

Sub MatchData()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, j As Long, isMatched 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
    
    ' 关闭屏幕更新提升速度
    Application.ScreenUpdating = False
    
    ' 遍历Sheet1每一行
    For i = 2 To lastRow1
        isMatched = False
        ' 遍历Sheet2找匹配项
        For j = 2 To lastRow2
            ' 检查A-E列匹配规则（空白视为匹配）
            If (ws1.Cells(i, "A") = ws2.Cells(j, "A") Or ws2.Cells(j, "A") = "") And _
               (ws1.Cells(i, "B") = ws2.Cells(j, "B") Or ws2.Cells(j, "B") = "") And _
               (ws1.Cells(i, "C") = ws2.Cells(j, "C") Or ws2.Cells(j, "C") = "") And _
               (ws1.Cells(i, "D") = ws2.Cells(j, "D") Or ws2.Cells(j, "D") = "") And _
               (ws1.Cells(i, "E") = ws2.Cells(j, "E") Or ws2.Cells(j, "E") = "") Then
                ' 匹配成功写入结果（这里输出到Sheet1的F列，可自行修改）
                ws1.Cells(i, "F") = ws2.Cells(j, "J")
                isMatched = True
                Exit For ' 找到匹配就跳出循环，减少不必要遍历
            End If
        Next j
        ' 无匹配则留空
        If Not isMatched Then ws1.Cells(i, "F") = ""
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "匹配完成！"
End Sub

使用步骤 #

• 按Alt + F11打开VBA编辑器，插入模块后粘贴代码
• 若需修改结果输出列，把ws1.Cells(i, "F")中的F改为目标列号

公式方案（仅临时使用，大数量不推荐） #

数组公式（需按Ctrl+Shift+Enter确认），仅适配单空白单元格场景，多空白需补充逻辑，10000+行卡顿明显：

=INDEX(Sheet2!$J:$J,MAX(IF((Sheet2!$A:$A=A2)*(Sheet2!$B:$B=B2)*(Sheet2!$C:$C=C2)*(Sheet2!$D:$D=D2)*(Sheet2!$E:$E=E2)+(Sheet2!$A:$A="")*(Sheet2!$B:$B=B2)*(Sheet2!$C:$C=C2)*(Sheet2!$D:$D=D2)*(Sheet2!$E:$E=E2)+(Sheet2!$A:$A=A2)*(Sheet2!$B:$B="")*(Sheet2!$C:$C=C2)*(Sheet2!$D:$D=D2)*(Sheet2!$E:$E=E2)+(Sheet2!$A:$A=A2)*(Sheet2!$B:$B=B2)*(Sheet2!$C:$C="")*(Sheet2!$D:$D=D2)*(Sheet2!$E:$E=E2)+(Sheet2!$A:$A=A2)*(Sheet2!$B:$B=B2)*(Sheet2!$C:$C=C2)*(Sheet2!$D:$D="")*(Sheet2!$E:$E=E2)+(Sheet2!$A:$A=A2)*(Sheet2!$B:$B=B2)*(Sheet2!$C:$C=C2)*(Sheet2!$D:$D=D2)*(Sheet2!$E:$E=""),ROW(Sheet2!$J:$J),0)))

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