Lookup & Loop

Attached is a sample of a workbook I'm trying to devise

Basically on Sheet 1 there are some query's that have been pulled from some DBF files and sorted accordingly

On Sheet 2 is the lookup I am struggling with.

What I'm trying to achieve is a VBA code that will reference Cell C1 then return the value 1 cell to the left of of this value in sheet 1 and then repeat until complete. I've added the actually value to be returned highlighted in yellow to clarify.

Many thanks