我使用与前一个答案相同的数据样本。

您还可以BYROW()循环遍历 sheet2 中的每一行来检查：

=XLOOKUP(TRUE, BYROW(Sheet2!$A$1:$E$4, LAMBDA(row, AND((A2:E2 = row) + (row = "")))), Sheet2!$I$1:$I$4)

工作原理如下BYROW()：

1. 它循环遍历 sheet2 中的每一行：
   

2. 对于每一行，它利用 Excel 公式的强大功能，使用单个算术运算符对一行中的所有单元格进行操作，以检查它们是否为空白或与 Sheet1 中的 A2、B2 等相同。

这是 G1 中的（数组）公式，并将

Sheet2 上的空单元格拖拽下来处理identical。

=IFERROR(INDEX(Sheet2!$J$1:$J$4,MATCH(CONCATENATE(A1,B1,C1,D1,E1),  
CONCATENATE(IF(Sheet2!$A$1:$A$4="",A1,Sheet2!$A$1:$A$4), 
IF(Sheet2!$B$1:$B$4="",B1,Sheet2!$B$1:$B$4), 
IF(Sheet2!$C$1:$C$4="",C1,Sheet2!$C$1:$C$4), 
IF(Sheet2!$D$1:$D$4="",D1,Sheet2!$D$1:$D$4), 
IF(Sheet2!$E$1:$E$4="",E1,Sheet2!$E$1:$E$4)),0)),"")

使用 VBA，您可以将此公式作为数组公式分配给所需范围

使用（正如Harun24hr所建议的）来获取每行的空白或等于数：

• 由于涉及整列的计算可能很慢，因此请首先获取包含数据的最大行数（如果需要，可以通过限制范围来进一步限制）：
  MAX(IF(NOT(ISBLANK(ae_2_all)), ROW(ae_2_all), 0)...
• MMULT(columns in A:E, five 1's for each row)用于按行求和
• ae_blank + ae_equal是使用OR(ae_blank, ae_equal)按行获取
• --用于转换TRUE/FALSE为1/0

输入G1（）：

=LET(
    ae_1, A2:E2,
    ae_2_all, Sheet2!A:E,
    j_2_all, Sheet2!J:J,
    last_row, MAX(
        IF(NOT(ISBLANK(ae_2_all)), ROW(ae_2_all), 0),
        IF(NOT(ISBLANK(j_2_all)), ROW(j_2_all), 0)
    ),
    j_2, INDEX(j_2_all, 1, 1):INDEX(j_2_all, last_row, 1),
    ae_2, INDEX(ae_2_all, 1, 1):INDEX(ae_2_all, last_row, 5),
    ae_blank, MMULT(--ISBLANK(ae_2), SEQUENCE(5, , 1, 0)) = 5,
    ae_equal, MMULT(--(ae_1 = ae_2), SEQUENCE(5, , 1, 0)) = 5,
    IF(ae_blank + ae_equal, IF(ISBLANK(j_2), "", j_2), "")
)

或者，不计算最后一行：

=LET(
    ae_1, A2:E2,
    j_2, Sheet2!J1:J10000,
    ae_2, Sheet2!A1:E10000,
    ae_blank, MMULT(--ISBLANK(ae_2), SEQUENCE(5, , 1, 0)) = 5,
    ae_equal, MMULT(--(ae_1 = ae_2), SEQUENCE(5, , 1, 0)) = 5,
    IF(ae_blank + ae_equal, IF(ISBLANK(j_2), "", j_2), "")
)

VBA 查找：多列查找

• 看起来为空的行不是，即CountBlank会返回大于 0 的数字，因为它们之前包含公式并且是使用 复制的PasteValues。否则，您无法使用CurrentRegion来引用完整范围。
• 如果设置ARE_BLANKS_A_MATCH为False，则17只会返回 ，因为它位于唯一一行中所有单元格都不为空白，因为单个空白单元格会使该行失去资格。
• 错误也会被匹配。但它们必须是同一类型。
• 可以随意使用列号（整数）代替列字符串或者混合使用（一个坏主意；最好保持一致）。
• 您还可以返回多列。

主要的

Sub MatchDataByColumns()

    ' Define constants.
    Const SRC_SHEET_NAME As String = "Sheet2"
    Const SRC_LOOKUP_COLUMNS As String = "A,B,C,D,E" ' or numbers
    Const SRC_RETURN_COLUMNS As String = "J" ' or numbers
    Const DST_SHEET_NAME As String = "Sheet1"
    Const DST_LOOKUP_COLUMNS As String = "A,B,C,D,E" ' or numbers
    Const DST_RETURN_COLUMNS As String = "F" ' or numbers
    Const COLUMNS_DELIMITER As String = ","
    Const CONCAT_DELIMITER As String = "\"
    Const ARE_BLANKS_A_MATCH As Boolean = True
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sJag() As Variant, dJag() As Variant, Cols As Variant, cUB As Long
    
    ' LOOKUP
    
    ' Reference the source range and retrieve the number of rows.
    Dim srg As Range, sRowsCount As Long
    With wb.Sheets(SRC_SHEET_NAME).Range("A1").CurrentRegion
        sRowsCount = .Rows.Count - 1
        If sRowsCount = 0 Then Exit Sub ' no data
        Set srg = .Resize(sRowsCount).Offset(1)
    End With
    
    ' Return the source lookup columns in an array.
    SplitStringToIntegers Cols, cUB, SRC_LOOKUP_COLUMNS, COLUMNS_DELIMITER
    
    ' Populate the source lookup array.
    JagColumnRanges sJag, srg, sRowsCount, Cols, cUB
    Erase Cols
    
    ' Return the concatenated source lookup columns in an array.
    Dim sData() As Variant: ConcatJaggedColumns _
        sData, sJag, sRowsCount, cUB, ARE_BLANKS_A_MATCH, CONCAT_DELIMITER
    Erase sJag
    
    ' Reference the destination range and retrieve the number of rows.
    Dim drg As Range, dRowsCount As Long
    With wb.Sheets(DST_SHEET_NAME).Range("A1").CurrentRegion
        dRowsCount = .Rows.Count - 1
        If dRowsCount = 0 Then Exit Sub ' no data
        Set drg = .Resize(dRowsCount).Offset(1)
    End With
    
    ' Return the destination lookup columns in an array.
    SplitStringToIntegers Cols, cUB, DST_LOOKUP_COLUMNS, COLUMNS_DELIMITER
    
    ' Populate the destination lookup array.
    JagColumnRanges dJag, drg, dRowsCount, Cols, cUB
    Erase Cols
    
    ' Return the concatenated destination lookup columns in an array.
    Dim dData() As Variant: ConcatJaggedColumns _
        dData, dJag, dRowsCount, cUB, ARE_BLANKS_A_MATCH, CONCAT_DELIMITER
    Erase dJag
    
    ' MATCH
    
    ' Return the source lookup matching row indices in an array.
    Dim srIndices() As Variant: srIndices = Application.Match(dData, sData, 0)
    drg.EntireRow.Columns("H").Value = sData
    drg.EntireRow.Columns("I").Value = dData
    drg.EntireRow.Columns("J").Value = srIndices
    Erase sData
    Erase dData
    
    ' RETURN
    
    ' Return the source return columns in an array.
    SplitStringToIntegers Cols, cUB, SRC_RETURN_COLUMNS, COLUMNS_DELIMITER
    
    ' Populate the source return array.
    JagColumnRanges sJag, srg, sRowsCount, Cols, cUB
    Erase Cols
    
    ' Return the destination return columns in an array.
    SplitStringToIntegers Cols, cUB, DST_RETURN_COLUMNS, COLUMNS_DELIMITER
    
    ' Define the destination return array.
    ReDim dJag(0 To cUB)

    ' Populate the destination return array with arrays.
    Dim drData() As Variant: ReDim drData(1 To dRowsCount, 1 To 1)
    Dim rc As Long
    For rc = 0 To cUB
        dJag(rc) = drData
    Next rc
    Erase drData
    
    ' Populate the destination return array with the matching values.
    JagMatches dJag, dRowsCount, srIndices, sJag, sRowsCount, cUB
    Erase sJag
    Erase srIndices
    
    ' Copy the values from each array in the destination return array
    ' to the correcponding return column range.
    For rc = 0 To cUB
        drg.Columns(Cols(rc)).Value = dJag(rc)
    Next rc
            
    ' Inform.
    MsgBox "Data matched.", vbInformation
    
End Sub

帮助

Sub SplitStringToIntegers( _
        ByRef SplitArray As Variant, _
        ByRef SplitUB As Long, _
        ByVal StringToSplit As String, _
        Optional ByVal SplitDelimiter As String = ",")
    
    Dim StringArray() As String:
    StringArray = Split(StringToSplit, SplitDelimiter)
    SplitUB = UBound(StringArray)
    
    Dim VariantArray() As Variant: ReDim VariantArray(0 To SplitUB)
    
    Dim c As Long, sStr As String
    For c = 0 To SplitUB
        sStr = StringArray(c)
        If IsNumeric(sStr) Then
            VariantArray(c) = CLng(StringArray(c))
        Else
            VariantArray(c) = StringArray(c)
        End If
    Next c

    SplitArray = VariantArray

End Sub

Sub JagColumnRanges( _
        ByRef Jag() As Variant, _
        ByVal rg As Range, _
        ByVal RowsCount As Long, _
        ByVal Cols As Variant, _
        ByVal cUB As Long)
    ReDim Jag(0 To cUB)
    Dim c As Long
    For c = 0 To cUB
        Jag(c) = GetRange(rg.Columns(Cols(c)))
    Next c
End Sub

Function GetRange(ByVal rg As Range) As Variant
    If rg.Cells.CountLarge = 1 Then
        Dim Data() As Variant: ReDim Data(1 To 1, 1 To 1)
        Data(1, 1) = rg.Value
        GetRange = Data
    Else
        GetRange = rg.Value
    End If
End Function

Sub ConcatJaggedColumns( _
        ByRef Data() As Variant, _
        Jag() As Variant, _
        ByVal RowsCount As Long, _
        ByVal cUB As Long, _
        ByVal AreBlanksAMatch As Boolean, _
        Optional ByVal ConcatDelimiter As String = "\")

    ReDim Data(1 To RowsCount, 1 To 1)
    Dim dlen As Long: dlen = Len(ConcatDelimiter)
    
    Dim sStr As String, tStr As String, r As Long, c As Long
    Dim HasRowValue As Boolean, IsValid As Boolean
    
    For r = 1 To RowsCount
        tStr = vbNullString
        IsValid = True
        HasRowValue = False
        For c = 0 To cUB
            sStr = CStr(Jag(c)(r, 1))
            If AreBlanksAMatch Then
                If Not HasRowValue Then
                    If Len(sStr) > 0 Then HasRowValue = True
                End If
            Else
                If Len(sStr) = 0 Then
                    IsValid = False
                    Exit For
                End If
            End If
            tStr = tStr & sStr & ConcatDelimiter
        Next c
        If AreBlanksAMatch Then
            If IsValid Then
                If Not HasRowValue Then IsValid = False
            End If
        End If
        If IsValid Then
            Data(r, 1) = Left(tStr, Len(tStr) - dlen)
        End If
    Next r
            
End Sub

Sub JagMatches( _
        ByRef drJag() As Variant, _
        ByVal dRowsCount As Long, _
        srIndices() As Variant, _
        srJag() As Variant, _
        ByVal sRowsCount As Long, _
        ByVal cUB As Long)

    Dim sRow As Variant, dRow As Long, rc As Long

    For dRow = 1 To dRowsCount
        sRow = srIndices(dRow, 1)
        If IsNumeric(sRow) Then
            For rc = 0 To cUB
                drJag(rc)(dRow, 1) = srJag(rc)(sRow, 1)
            Next rc
        End If
    Next dRow

End Sub

副产品：数组和 Split 函数

• 将其复制到新工作簿并打开 VBE。
• 另外，打开“立即”窗口和“本地”窗口。
• 运行代码并在每次停止时查看注释和窗口中发生的情况并继续F5。
• 您还可以使用 逐步执行代码F8。

Sub Test()
    Dim ErrCount As Long
    On Error GoTo ClearError ' start error-handling routine

    Const STRINGS As String = "A,B,C"
    Const DIGITS As String = "1,2,3"
    Const MIXED As String = "1,A,3"
    Const DELIMITER As String = ","
    Const MAX_ERRORS_ALLOWED As Long = 20
    
    Dim anInteger As Long, IsArrayAllocated As Boolean
    
    ' Basic
    
    Dim Arr() As String
    
    ' An attempt to retrieve the limits from an unallocated array produces
    ' 'Run-time error '9': Subscript out of range'.
    Debug.Print "[LB=" & LBound(Arr) & ", UB=" & UBound(Arr) & "]"
    
    Stop
    
    ' To check if an array is allocated you could use something like this:
    On Error Resume Next ' defer error trapping
        anInteger = LBound(Arr)
        IsArrayAllocated = (Err.Number = 0)
    On Error GoTo 0 ' enable error trapping
    Debug.Print "The array is " & IIf(IsArrayAllocated, "", "not") _
        & " allocated."
    
    On Error GoTo ClearError ' restart error-handling routine
    
    Stop
        
    ' Declaring the variable as a Variant without parentheses produces
    ' a Variant holding a Variant array ('Variant/Variant()').
    Dim ArrVarVar As Variant: ReDim ArrVarVar(0 To 1)
    ArrVarVar(0) = 1: ArrVarVar(1) = 2
    Debug.Print "ArrVarVar", VarType(ArrVarVar), TypeName(ArrVarVar)
        
    Stop
        
    ' Declaring the variable with parentheses produces
    ' an array of the specified type (e.g. 'Variant()' or 'Long()').
    
    Dim ArrVar() As Variant: ReDim ArrVar(0 To 1)
    ArrVar(0) = 1: ArrVar(1) = 2
    Debug.Print "ArrVar", VarType(ArrVar), TypeName(ArrVar)
        
    Dim ArrLng() As Long: ReDim ArrLng(0 To 1)
    ArrLng(0) = 1: ArrLng(1) = 2
    Debug.Print "ArrLng", VarType(ArrLng), TypeName(ArrLng)
        
    Stop
    
    ' Declaring the variable as a Variant without parentheses
    ' and assigning an array of a specified type to it produces
    ' a Variant holding this specifically typed array ('Variant/Long()').
    Dim ArrVarLng As Variant: ArrVarLng = ArrLng
    Debug.Print "ArrVarLng", VarType(ArrVarLng), TypeName(ArrVarLng)
    
    ' Note how the VarType and TypeName functions return the same
    ' for each array type.
    ' Open the Locals Window to see the difference.
    
    Stop
    
    ' The Split Function
        
    ' The result of the Split function is always a zero-based array
    ' even if Option Base is set to 1
    ' (the same goes for the Array function if '... = VBA.Array(...)' is used).
        
    ' Declaring the variable with as a Variant or any other type
    ' different than the String type produces
    ' 'Run-time error '13': Type mismatch': WRONG.
    Dim Arr13() As Variant: Arr13 = Split(STRINGS, DELIMITER)
    
    Stop
    
    ' Declaring it with 'Dim Arr As Variant' produces
    ' a Variant holding a String array: OK.
    Dim ArrV As Variant: ArrV = Split(STRINGS, DELIMITER)
    Debug.Print "ArrV", VarType(ArrV), TypeName(ArrV)
    
    Stop
    
    ' Declaring it with 'Dim Arr() As String' produces a String array. OK.
    Dim ArrS() As String: ArrS = Split(STRINGS, DELIMITER)
    Debug.Print "ArrS", VarType(ArrS), TypeName(ArrS)
    
    ' Again, the VarType and TypeName functions return the same.
    ' See the difference in the Locals Window.
    
    Stop
    
    ' Splitting an empty string results in an allocated array
    ' with the upper limit smaller than the lower limit.
    Arr = Split("", DELIMITER)
    ' Let's prove this.
    
    ' Result:  [LB=0, UB=-1]
    Debug.Print "[LB=" & LBound(Arr) & ", UB=" & UBound(Arr) & "]"

    Stop

    ' Let's now check again if the array is allocated. We know it is
    ' because the previous Debug.Print line didn't raise an error.
    On Error Resume Next ' defer error trapping
        anInteger = LBound(Arr)
        IsArrayAllocated = (Err.Number = 0)
    On Error GoTo 0 ' enable error trapping
    Debug.Print "The array is " & IIf(IsArrayAllocated, "", "not") _
        & " allocated."
    
    On Error GoTo ClearError ' restart error-handling routine

    Stop

    ' Now let's cut to the chase. I started this to see
    ' whether the digits from the split Strng array will be automatically
    ' converted to integers when copied to a Variant array.
    Arr = Split(DIGITS, DELIMITER)
    
    Dim ArrL As Variant: ReDim ArrL(0 To UBound(Arr))
    
    Dim i As Long
    
    For i = 0 To UBound(Arr)
        ArrL(i) = Arr(i)
        Debug.Print ArrL(i), VarType(ArrL(i)), TypeName(ArrL(i))
    Next i
    
    ' Answer: They won't. So we'll have to convert them to Longs.
    
    Stop
    
    For i = 0 To UBound(Arr)
        ArrL(i) = CLng(Arr(i))
        Debug.Print ArrL(i), VarType(ArrL(i)), TypeName(ArrL(i))
    Next i
    
    Stop
    
ProcExit:
    
    Exit Sub
ClearError: ' continue error-handling routine
    ErrCount = ErrCount + 1
    If ErrCount > MAX_ERRORS_ALLOWED Then
        MsgBox "Run-time error ]" & Err.Number & "[:" & vbLf & vbLf _
            & Err.Description, vbCritical
        Resume ProcExit
    Else
        MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf _
            & Err.Description, vbCritical
        Resume Next
    End If
End Sub