1. An extra open parentheses (red) in the line:
Sheets("Result file").Cells(k, 5).Value = WorksheetFunction.Index(Sheets("Data source").Range("E:E"), WorksheetFunction.Match(Sheets("Data source").(Cells(k, 1).Value, Range("A:A"), 0))
which shouldn't be there.
2.Unqualified range references in blue above, so they refer to whichever happens to be the active sheet at the time the code is executed.
3. WorksheetFunction.Index and WorksheetFunction.Match will both throw an error and stop the code executing if there is no match found. Use Application.Index and Application.Match instead (they still return an error but the code isn't halted).
4. Those changes leave:
Sheets("Result file").Cells(k, 5).Value = Application.Index(Sheets("Data source").Range("E:E"), Application.Match(Sheets("Data source").Cells(k, 1).Value, Sheets("Data source").Range("A:A"), 0))
which is looking at the wrong sheet for what to match, and should be:
Sheets("Result file").Cells(k, 5).Value = Application.Index(Sheets("Data source").Range("E:E"), Application.Match(Sheets("Result file").Cells(k, 1).Value, Sheets("Data source").Range("A:A"), 0))
This should work all right but it's long-winded.
You could get the code to put the formula in the cells, then convert the results of the formulae to plain values:
Sub blah()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheets("Result file")
Set rngResult = Range(.Range("A2"), .Range("A1").End(xlDown)).Offset(, 4)
End With
rngResult.FormulaR1C1 = "=INDEX('Data source'!C,MATCH(RC1,'Data source'!C[-4],0))" 'put the formulae in the cells.
rngResult.Value = rngResult.Value 'convert to plain values.
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
but it gets easier than that; since they're both Tables you can:
Sub blah2()
With Range("Table1[Law Reference]")
.FormulaR1C1 = "=INDEX(Table24[Law Reference],MATCH([@Fund],Table24[Fund],0))"
.Value = .Value
End With
End Sub