VBA excel - match based on 3 criterias and retrieve values from another workbook

I have two separate workbooks saved in the same file location (Workbook 1 and Workbook 2)

what i'm looking for is a macro that searches for a VehicleKey (unique key number) by 3 criteria - YRMANF(year of manufacture) , CAPCTY(car capacity), brand(car brand) from workbook1(macro for car) to YearGroup(year of manufacture) , EngineSize(car capacity), MakeDescription(car brand) from workbook 2(redbook).

Workbook 2 consist of about 10000 rows of data and my current method of looping is too slow.

If a match is found then I want the corresponding values from column A in Workbook 2 to be copied onto workbook 1's column J. Here is the values populated in Workbook1,Worksheet1.jpg
then matched in Workbook2 worksheet2.jpg

Workbook 2 won't be opened by the user, they will just click a button in Workbook1 and it will populate the data.

I currently have this working but with Vlookups which has greatly slowed down opening workbook 1.
My current work is:

Please Login or Register  to view this content.

any help is appreciated! Thank you.