VBA 1004 error : Application defined or object defined error

A few things wrong there:

First, you're using the literal text "Cells(i, 1).Value" and "Cells(1, j).Value" in the formula string. You would need to concatenate the values into the string like this:

Cells(i, j).FormulaR1C1 = "=vlookup(" & Cells(i,1).Value & ",SKULifeCycle_Table_Temp!R1C1:R5000C500,match(" & Cells(1,j).Value & ",'SKULifeCycle_Table_Temp'!R1C1:R1C500,0),0)"

Second, you used the .Formula property but passed R1C1 style references, so you should use the .FormulaR1C1 property (as I did above).

Third, if the values in the cells that you are using for the lookup values are text, you need to enclose them in quotes:

    Cells(i, j).FormulaR1C1 = "=vlookup(""" & Cells(i,1).Value & """,SKULifeCycle_Table_Temp!R1C1:R5000C500,match(""" & Cells(1,j).Value & """,'SKULifeCycle_Table_Temp'!R1C1:R1C500,0),0)"

or use the addresses (in R1C1 format) instead:

Cells(i, j).FormulaR1C1 = "=vlookup(RC1,SKULifeCycle_Table_Temp!R1C1:R5000C500,match(R1C,'SKULifeCycle_Table_Temp'!R1C1:R1C500,0),0)"

One issue that is clear (maybe the cause of the whole error), is that variables are being used within a string. So, what does that mean? I'll simplify using the code below as a template:

Sub Test1()
    Dim i as Integer
    Dim j as Integer

    i = 2
    j = 3

    Cells(i, j).Value = "The row number is i and the column number is j"
End Sub

In the Cells line, it correctly refers to cell C2, which is the second row and third column. But what value is inserted into the cell? It's literally "The row number is i and the column number is j" using the letters, and not their corresponding numbers. Similarly, in your case, the formula will read: VLOOKUP(CELLS(i,1)... using the letter i, which is incorrect.

Additionally, a cell's formula doesn't use the CELLS syntax. You instead need to build a string using the variables. It would look a lot like:

"=VLOOKUP(" & Cells(i, 1).Address & "SKULifeCycle_Table_Temp!R1C1:R5000C500, Match(" & Cells(i, 1)Address ... "

Try to build out your formula string and refer back to this post with any specific questions.