you could do a index and match (vlookup) fairly easy which is probably how most people have done it for the last 20 years, but it can slow down a work book and you would need to do it again each time - this can be automated with VBA but takes some development time.
If the main goal is just merging the 2 tables i would look at power query - i have not spent that much time using it but for table merges it looks ideal.
You can also append data to the tables and refresh - not sure how you automate the joining new data extracts with power query but i am sure its possible you can even import directly from a folder.
Have a look at this blog post
https://www.myonlinetraininghub.com/...e-excel-sheets