Help with Index Match function!

[sllaksvb]

Hi all,

I am trying to use index match function to index column A of Sheet 2, and match each value of column E in Sheet 1 with the values of column C in Sheet 2. And if the codes match, to return the value in column A of sheet 2 into the corresponding row in sheet 1. I can't seem to get the syntax right for the index match function, would appreciate if someone could help!



Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
RowNum = Selection.Rows.Count + 2

For i = 3 To RowNum

Sheets("Sheet1").Cells(i,3).Value = Application.WorksheetFunction.Index(Sheets("Sheet2").Range ("A:A"), Application.WorksheetFunction.Match(Sheets("Sheet1").Cells(i, 5), Sheets("Sheet2").Range("C:C"), 0)

Next i

[mdmackillop]

Can you provide sample data.

[sllaksvb]

Sorry, I forgot to attach the sample data file!2028920289

[mdmackillop]

Sub Test1()
Dim r As Range
With Sheets("Sheet1")
Set r = Range(.Cells(3, 5), .Cells(3, 5).End(xlDown)).Offset(, -4)
End With
r.FormulaR1C1 = "=INDEX(Sheet2!C,MATCH(RC[4],Sheet2!C[2],0))"
r.Value = r.Value
End Sub