Yeah...Figured that out, but tried deleting the extra sets with no different result. But, finally figured out the problem. When I was recording this monster vlookup formula, I should've written it in the cell first, copied and pasted to word. Then go back to excel, hit record, Pasted it in the formula bar, not just click once on the cell. Then Excel will write it in VBA for me, and R1C1, which is what I was trying to do all along. Thanks everyone for your help! This is the code I ended up with that works!
Musicman715...Solved...Can anyone tell me how to mark this "Solved"?
Y5 = Range("A1", Range("A1").End(xlDown)).Rows.Count

    For n = Y5 - 3 To 2 Step -1
    Cells(n, 4).FormulaR1C1 = "=VLOOKUP(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(RC[-2],"" "",REPT("" "",LEN(RC[-2]))),LEN(RC[-2]))),TRIM(RIGHT(SUBSTITUTE(RC[-2],"" "",REPT("" "",LEN(RC[-2]))),LEN(RC[-2]))),""*""&TRIM(RIGHT(SUBSTITUTE(RC[-2],"" "",REPT("" "",LEN(RC[-2]))),LEN(RC[-2])))&""*""),'[event master lookup table.xls]Sheet1'!C1:C4,4,FALSE)"
    Next n