Slow Load and running with VBA and Vlookups

mehmetcik, thank you for taking the time to assist and I really appreciate it. I updated my code with your revisions and find the code is a lot cleaner (something I still need to work on but am learning) and it did speed it up by approx. 3-4 minutes but its still taking a lot of time for the file to load, update when I depress the 'update' button and even more time to save when I choose save and close. I don't know if there is anything else that can be done at this point since it is a huge file being 100 megs in size and won't shrink dramatically.
Again thank you for taking the time and effort and if you or anyone else have any other ideas I would appreciate the advise.

All,

After multiple attempts to improve the speed of this massive file and all the help everyone has given me I decided to split the file. So what I did was take the 100 meg file and made a new xlsx file and copied all the data as values to the new workbook and am now using it as a 'database' and yes I hate to call it that since excel is not meant to be a database but it will do for now till I can build the access database if they are willing to accept the obvious changes that are required. So this way the 'database' is now just that, its only a database aka repository in my world. I then took a new workbook and created the headers with no data below it. When the data gets filled in they run from a command button to do the Index Match formulas (thank you xlnitwit for your assistance and the code) and also thank you mehmetcik as well for the alternative workaround as well but the Index Match does perform faster so I went with that version. Both are good methods but I had to go with the faster version. So then when they run the code from the command button it populates the rows as per the code and I took it a step further to transfer the data to the repository with the following code:

Please Login or Register  to view this content.

This method works the fastest as all the formulas are now in the small version and then transferred to the master repository aka archive aka database as values without taking along time. It's an extra workbook created but it does what is needed and anyone can still retrieve the data from the MTUMaster that houses all the values.
Again, thank you all for your help and ideas and yes bakerman2 thank you as well as I did try the array into memory also.
I am closing this as solved thanks to everyone for the great ideas, alternative codes and making me think deeper and not so one dimensional all the time

Now to convince the powers that be too start using Access for alot of these workbooks that are being treated as databases.