Macro help, for look up and index?

Try this and copy it down.

=VLOOKUP(LEFT(C2,2),$A$2:$B$11,2,FALSE)&","&VLOOKUP(MID(C2,4,2),$A$2:$B$11,2,FALSE)&","&VLOOKUP(RIGHT(C2,2),$A$2:$B$11,2,FALSE)

Here is some code that works for me, if you make the change to Column A as advised.

Sub foo()

'  Code developed by ASidman 11/11/13

    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Dim lra As Long
    lra = Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Range("D2:D" & lr).FormulaR1C1 = "=VLOOKUP(LEFT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(MID(RC[-1],4,2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(RIGHT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)"
    
End Sub

My apologies. I was, too. I forgot to mention to get this to work, you need to have column A as text. With your current set up you have column A as a number and column C as text. You will need to change column A to text. .ie, '01, '02, etc. This can easily be converted to VBA, but you will need to fix column A to make it work in that environment also. Give me a few minutes to write up some VBA code.

Would you always have four numbers or could it be mixed, some rows with four and some rows with three?

This will make a big difference in how the VBA is written because if it is mixed, then the code will need to test for the length of the string and decide on the formula to apply. In either case, it can be accomplished.

Assuming you have a mixed bag of numbers, then you would use this code:

Sub foo()
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Dim lra As Long
    lra = Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long
        For i = 2 To lr
            If Len(Range("C" & i)) > 10 Then
                Range("D" & i).FormulaR1C1 = "=VLOOKUP(LEFT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(" & _
                "MID(RC[-1],4,2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""& vlookup(" & _
                "mid(RC[-1],7,2),R2C1:R[" & lra & "]C2,2,False)&"",""& VLOOKUP(" & _
                "RIGHT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)"
            Else
                Range("D" & i).FormulaR1C1 = "=VLOOKUP(LEFT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(" & _
                "MID(RC[-1],4,2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&  VLOOKUP(" & _
                "RIGHT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)"
            End If
        Next i
End Sub

Here is the solution to the last question

Sub foo()
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Dim lra As Long
    lra = Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Range("D2:D" & lr).FormulaR1C1 = "=VLOOKUP(LEFT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(" & _
    "MID(RC[-1],4,2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(" & _
    "MID(RC[-1],7,2),R2C1:R[" & lra & "]C2,2,FALSE)&"",""&VLOOKUP(" & _
    "RIGHT(RC[-1],2),R2C1:R[" & lra & "]C2,2,FALSE)"
    
End Sub