you are using FormulaR1C1 method
i assume because you just recorded marco and used whatever it gave you and tried to amend it
thats ok....its how i learnt also

R = Row
C = Column
R(0)C(-3) = 0 rows, 3 columns back..relative to your range

take this line of code
and assuming rownum is 30

Range("P" & RowNum).FormulaR1C1 = "= VLOOKUP(RC[-3],[MISSINGUSERNAMEDEPT.xlsx]Sheet1!C1:C5,2,0)"
range would (P30)
RC[-3] = p30 + zero rows + (- 3 columns) = M30
explained from above C1:C5 = A:E

the formula now reads
vlookup(m30,[Workbook]Worksheet!A:E,2,0)

more reading about it can be found here
https://excelmate.wordpress.com/2013...e-style-vs-a1/

alternatively don't use R1C1 just use .formula instead

Range("P" & rownum).Formula = "= VLOOKUP(" & Range("M" & rownum).Address & ",[MISSINGUSERNAMEDEPT.xlsx]Sheet1!A:E,2,0)"