VBA - Using index/match with multiple match criteria with loop

[vba_andy]

Hello all, this is my first post on this website, as I can usually find all questions on previous threads, but finally found one I am completely stuck. I'm a relative beginner so hope anyone can give some feedback on my problem.

I am searching for the 'AMT' by matching the 'REF' and 'EVENT' which are all in different columns.
It will loop through each row and put result in another column ('C' in this example)

I am not sure how to make INDEX function to work with two MATCH criteria.
The code seems almost there since it works with one.

Please share if you can help, thank you !


Dim i, lastRow, var1, var2 As Integer
Dim match1 As Double
Dim match2 As String
i = 2
lastRow = Sheets("Home").Range("A" & Rows.Count).End(xlUp).row
Set col_amt = Sheets("Home").Range("P:P")
Set col_ref = Sheets("Home").Range("Q:Q")
Set col_event = Sheets("Home").Range("R:R")

For i = 2 To lastRow

match1 = Cells(i, 2)
match2 = Cells(i, 9)
var1 = WorksheetFunction.Match(match1, col_ref, 0)
var2 = WorksheetFunction.Match(match2, col_event, 0)

Cells(i, 3).Value = WorksheetFunction.Index(col_amt, var2, var1)

Next