Change lookup formula to VB

I am currently using the following lookup formula:
=IFERROR(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)&"","")

However I would like to change this action to take place using VB in the worksheet change event code. The reason being is because this formula runs extremely slowly (because I need it to find a match starting from the lowest rows, not from the top), and sometimes the formula in the cell gets accidentally erased.

I currently already have the following code in the change event for that sheet which it would need to work alongside:

Please Login or Register  to view this content.

The sheet I'm working on is called ReturnData, and the formula is in the F column (from 6 down).
I have attached my workbook.
Thank you for you time.

Please Login or Register  to view this content.

Where A1 is the cell you want the formula to apply to.

That didn't seem to do anything for me. The column I want the formula in is F (from rows 6 down). I tried changing the formula to the following but it didn't work either:
Range("$F6").formula = "=IFERROR(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)&"""","""")"
Range("$F6").value = Range("$F6").value

Will this work quicker that just having the formula in the cells? Because currently this formula almost grinds this workbook to a holt. Im assuming because its actually looking at every row in EquipmentData from the bottom of the sheet up. Can this not be done from scratch with some VB code so it just starts the search from the last record?
If it helps I already have two dynamic ranges for that sheet already, one called EquipmentList which lists every record in C, and one called SerialNumbs which lists every record in B.