CODEWITHSUNDEEP
excelvbaexcel-formula
surendra choudhary
surendra choudhary

Reputation: 65

Matching values with multiple columns

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. enter image description here

Upvotes: 1

Views: 90

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 2

Related Questions