Get value frome one table, to another based on the same value of 2 cells

Question

I've got one table with 3 columns, and another with 2 columns and I want to get value from table 1 to table2 if column 1 & column 2 values are the same as table2 column 1 & column 2.

As above, in table 2 I need to get values from table 1. Eg. Table 1 cat lion is 3, I want to copy 3 to each row in table 2 where cat lion is. Can it be achieved by formula or vba code?

Answer 1

• VBA Code

Option Explicit
Sub Demo()
    Dim objDic As Object, rngData As Range
    Dim i As Long, sKey As String
    Dim arrData
    Set objDic = CreateObject("scripting.dictionary")
    Set rngData = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
    ' Load table1 into an arraya
    arrData = rngData.Value
    ' Load data into Dict object
    For i = LBound(arrData) To UBound(arrData)
        sKey = arrData(i, 1) & "|" & arrData(i, 2)
        objDic(sKey) = arrData(i, 3)
    Next i
    ' Load table2
    Set rngData = Range("E2:G" & Cells(Rows.Count, "E").End(xlUp).Row)
    arrData = rngData.Value
    ' Loop through data
    For i = LBound(arrData) To UBound(arrData)
        sKey = arrData(i, 1) & "|" & arrData(i, 2)
        If objDic.Exists(sKey) Then _
            arrData(i, 3) = objDic(sKey)
    Next i
    ' Write ouput to sheet
    rngData.Value = arrData
End Sub