Nested Vlookups?

I was trying to "nest" some vlookup functions so that i could check for a value in multiple worksheets but after writing the code i realized that it returns N/A, when i would have expected it to move to the next vlookup. I understand why it does this (because once its pasted in the cell it doesnt know what the functions are) but i dont know how to correct it. I would prefer to not even past the formula in the cell but that is beyond my ability. Here is the code:

Sub CompareLists()
    CompCore
End Sub


Public Function CompCore()
    MaxRowNumber = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    AutoFillRange = "G1:G" & MaxRowNumber
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R[0]C[-6], [Multi_Record_Macro.xlsm]Core!R1C1:R500C1, 1, FALSE)), CompReplace, ""CORE"")"
    Range("G1").Select
    Selection.AutoFill Destination:=Range(AutoFillRange), Type:=xlFillDefault
End Function

Public Function CompReplace()
    MaxRowNumber = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    AutoFillRange = "G1:G" & MaxRowNumber
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R[0]C[-6], [Multi_Record_Macro.xlsm]Replace!R1C1:R500C1, 1, FALSE)), CompNoInstall, ""REPLACE"")"
    Range("G1").Select
    Selection.AutoFill Destination:=Range(AutoFillRange), Type:=xlFillDefault
End Function

Public Function CompNoInstall()
    MaxRowNumber = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    AutoFillRange = "G1:G" & MaxRowNumber
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R[0]C[-6], [Multi_Record_Macro.xlsm]NoInstall!R1C1:R500C1, 1, FALSE)), ""QUESTION"", ""REMOVE"")"
    Range("G1").Select
    Selection.AutoFill Destination:=Range(AutoFillRange), Type:=xlFillDefault
End Function

I have 2 posts and i haven't gotten a response for either. I don't mean to be impatient. I just want to make sure I haven't done anything wrong to cause people to avoid helping me. These are my first posts. If anyone could kindly point me in the right direction I would appreciate the help. Thanks