Option Explicit
Sub Macro1()
Dim iRange As Range
Dim iSheet As Worksheet  'Input Sheet Handle
Dim oSheet As Worksheet  'Output Sheet Handle
Dim aCell As Range
Dim tStr As String
Dim aRow As Long
Dim aCol As Long

'Set the handle and range on the Input Sheet
Set iSheet = ActiveWorkbook.Sheets("Sheet2")
Set iRange = iSheet.Range("C1:Z26")
'Set the handle to the Output Sheet
Set oSheet = ActiveWorkbook.Sheets("Sheet1")

'Iterate through the Input Range
For Each aCell In iRange
'If the cell contains a number in the range of 1 to 26
If aCell.Value  0 And aCell.Value <= 26 Then
'These are the references for the source cell:
aRow = aCell.Row
aCol = aCell.Column
'Pull the text from Column A from the row enumerated in the
current cell
tStr = iSheet.Cells(aCell.Value, "A").Text
'TEST TEST TEST TEST
'MsgBox tStr
'END TEST
'Here is where you load Sheet 1, but you don't say where to put
the data ???
'This will load your lookup text into the same relative cell
reference on Sheet 1
'You need to figure out exactly what you want to do here ...
oSheet.Cells(aRow, aCol).Value = tStr

Else
tStr = ""
End If
Next

End Sub