Like I said I know the formula I posted isn't right.

Do you have the formula in a cell?

Are Grids and Elements named ranges?

This will work, both in VBA and in a cell, but I don't know if it's the formula you want.
Formula: copy to clipboard 


=AVERAGE(VLOOKUP(INDEX(Grids, MATCH(C3, Elements, 0), 1), TEMP, 2, FALSE))




    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    Range("C2").Value = "AVG TEMP"  'Named this Column

    Range("C3:C" & LastRow).FormulaR1C1 = "=Average(VLookup(Index(Grids, Match(R1C, Elements, 0), 1), TEMP, 2, False))"