Matching values with multiple columns

Question

To match values of two columns from Range A with another two columns from Range B and copy third column value from range B against matched row

I'm trying to To match the values of two-column from Range A with another two columns from Range B and copy the third column value with the cell link as shown in the highlighted cell in the attached screenshot. I've able to do this, but I'm not close to solving this.

 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

Please help. Any type of help will be appreciated.

Vlookup doesn't return a Range, so you can't chain Offset() to it. — Tim Williams
Okay @Tim Williams How to change it, so that I can use offset or is there any method to select next column value in same row — surendra choudhary
You can do this more easily with a two-column MATCH()formula - eg exceljet.net/formula/index-and-match-with-multiple-criteria — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2021-02-01T17:28:11

Multi-column Match() using Evaluate:

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

Matching values with multiple columns

1 Answers