It doesn't sound complicated, however, 'my' answer may not be what you want to hear...
I use VBA a lot however, when dealing with larger datasets and matches, I tend to use Power Query.
My logic for the attached PQ file is:
1, Put all of your data from both sheet1 and sheet2 into table objects and not just data in a range.
2, Load table1 into PQ as a connection only and separate the file name from the folder path as a new column. This is done by splitting the 'Folder Path' column based on the rightmost delimiter of '\'.
3, Load table2 into PQ as a connection only.
4, Merge table2 with table1 based on the file name.
5, Expand the merged table and select only the 'Ref#' column.
6, Export the completed merge as a table to a new sheet.
PQ is super efficient and has replaced a lot of my use for VBA, IMO it is worth learning some PQ methods. ChatGPT can be helpful when learning how to complete specific tasks in PQ, for example you could ask ChatGPT "How to split a column based on a delimiter with Power Query" or "How to merge two tables using Power Query"
I have a attached the file for reference just in case you wanted to pick through it.
