How to convert Index(match.. (Left(Right...)..)..) into VBA (excel)

[p45cal]

try:
Sheets("Existing 2W").Range("S2").Value = WorksheetFunction.Index(Range("Latest_Range"), WorksheetFunction.Match(Left(Right((Sheets("Existing 2W").Range("Z2").Value), 11), 5), Range("LatestLineNo"), 0), 11)

(the .Value needed to be moved too).

Using WorksheetFunction.Match will stop the code running if no match is found, likewise with WorksheetFunction.Index.
Using Application.Match and Application.Index instead will be more forgiving:

Sheets("Existing 2W").Range("S2").Value = Application.Index(Range("Latest_Range"), Application.Match(Left(Right((Sheets("Existing 2W").Range("Z2").Value), 11), 5), Range("LatestLineNo"), 0), 11)