excel - 匹配多列的值

Question

将范围 A 中两列的值与范围 B 中的另外两列相匹配，并将范围 B 中的第三列值复制到匹配的行
我正在尝试将范围 A 中的两列值与范围 B 中的另外两列相匹配，并使用单元格链接复制第三列值，如所附屏幕截图中突出显示的单元格所示。
我能够做到这一点，但我还没有接近解决这个问题。

 Sub TRIAL_Example3()
 Dim k As Integer

 With Worksheet("TRIAL")
 Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row

 For k = 2 To Lastrow
 If (Cells(k, 1).Value = WorksheetFunction.VLookup(Cells(k, 1).Value, Range("F2:H" & Lastrow),1, 0)) 
  And (Cells(k, 2).Value = WorksheetFunction.VLookup(Cells(k, 1).Value, Range("F2:H" & Lastrow),1, 
  0)).offset(1,0) Then
  Cells(k, 11).Value = WorksheetFunction.VLookup(Cells(k, 1).Value, Range("F2:H" & Lastrow), 1, 0)
  Cells(k, 12).Value = WorksheetFunction.VLookup(Cells(k, 1).Value, Range("F2:H" & Lastrow), 1, 
     0).Offset(1, 0)
  Cells(k, 13).Value = WorksheetFunction.VLookup(Cells(k, 1).Value, Range("F2:H" & Lastrow), 1, 
   0).Offset(2, 0)
 Else:
Next k

End Sub

请帮忙。任何类型的帮助将不胜感激。

Answer 1

使用 Evaluate 的多列 Match():

Sub TRIAL_Example3()

    Dim k As Long, m, ws As Worksheet, rngTable As Range, frm, LastRow  As Long
 
    Set ws = Worksheets("TRIAL")

    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    Set rngTable = ws.Range("F2:H" & LastRow) 'lookup table
    
    'build a multi-column MATCH formula (<rw> is placeholder)
    frm = "=MATCH(A<rw>&B<rw>," & rngTable.Columns(1).Address & _
                            "&" & rngTable.Columns(2).Address & ",0)"
    
    For k = 2 To LastRow
        m = ws.Evaluate(Replace(frm, "<rw>", k)) 'replace row token with row# 
 
        If Not IsError(m) Then 
            m = rngTable.Columns(3).Cells(m) 'get value from third column
        else
            m = "No Match"
        End If  
        
        With ws.Rows(k)
            .Columns("K").Resize(1,3).Value = Array(.Columns("A"), .Columns("B"), m)
        End With
    Next k

End Sub