Using Match funtion in VBA; getting inconsistent results

Workbook 1 holds a table of information. Workbook 2 is where I run the macro. It temporarily opens Workbook 1 to return information using the Match function, populating column AF in workbook 2 based on whether a match is found.

For each row where a match is found in workbook 1, workbook 2 should populate column AF with the text "360 on-hand".
The problem is that the macro will return "360 on-hand" for some of the matches, and will not for others that in fact do match, leaving column AF blank. I have highlighted yellow about 5- 6 examples in both workbooks where the Match should work, but does not. The “match” is simply ignored.

For the code to work, please edit the path to point to wherever workbook 1 resides. Then open workbook 2 and run the macro.

Workbook 1 name is “J26 360 ThermoType database copy.xls”
Workbook 2 name is “export.xls"

Code is as follows:

Sub DIE_360_ON_HAND_TEST()
'THIS MACRO RUNS A LOOKUP IN THE 360 DIES TO SEE WHICH ARE ON-HAND

    Dim FinalRow As Long
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

    Application.ScreenUpdating = False

'OPEN TABLE OF 360’S
    Workbooks.Open filename:="YOUR PATH HERE\J26 360 ThermoType database copy.xls"
 
    Windows("EXPORT.xls").Activate

'REMOVE AUTOFILTER
    Rows("1:1").Select
    Range("E1").Activate
    Selection.AutoFilter

    
‘CLEAR AND RENAME COLUMN AF
    Range("AF:AF").ClearContents
    Range("AF:AF").ColumnWidth = 13
    Range("AF1").FormulaR1C1 = "360 DIE?"
    

'INSERT FORUMULA AT COLUMN AF    
    Range("AF2", Cells(FinalRow, "AF")).FormulaR1C1 = _
        "=IF(RC[3]<>""360"","""",IF(ISNA(MATCH(SUBSTITUTE(RC[-21]," _
        & """-"",""""),'[J26 360 ThermoType database copy.xls]360_2012'!C1,0))" _
        & ",""please add design"",""360 ON-HAND""))"

    
    
‘MAKE RESULT VALUES INSTEAD OF FORMULAS
Range("AF2:AF" & FinalRow).Value = Range("AF2:AF" & FinalRow).Value

  
'Close 360 database without saving
    Workbooks("J26 360 ThermoType database copy.xls").Close False


'ADD AUTOFILTER
    Rows("1:1").Select
    Range("E1").Activate
    Selection.AutoFilter

Application.ScreenUpdating = False
    
End Sub

J26 360 ThermoType database copy.xls
Attachment 399906

PLease dont just post files. Explain what you want, what you have tried, and what didnt work. At least try and do SOME of the work here

haha ok, no problem, I have clicked save before i was finished, myself