Huge data index/match replacement

Sorry, i'm making use of the data in an past post,how can i change the 2-dimension index/match to vba?
i know how to do it with formula (as attached logic) but don't know how i can shorten the process time.... i have >40,000 rows in the file and need to pops up results for >20 columns..
Attachment 529906

I am moving this to the VBA section.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Maybe :

Please Login or Register  to view this content.

You are welcome.
Of course the macro on post #3 won't work for your newest file on post #4, they have different layout.
This is the macro for your newest file on post #4 :

Please Login or Register  to view this content.

How do you get the error ?
Is it :

1. You download my attachment on post #5 and run the macro, and you got the error

or

2. You apply the macro on post #5 to your actual workbook, and you got the error

If it is the number 2, then you must upload your actual workbook, so I can look for the cause (probably caused by a different data layout).