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

[Alternsti]

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)

Both of your solutions are working fine. I'll stay with Application.Match and Application.Index.

Thank you very much for the help.