reduce the time to lookup value

Hi all,

Recently i am facing 500k pieces of rows of data,
which every row consists of around 20 columns.


Normally we use index,match or vlookup or loop to get the data link together.

I am thinking is there a better way to shortern time to find every single of data by loop ,whatever.

May be according to the reference key, and do the sorting, and kick the useless data out from the extraction?

Attached is my example worksheet.
Hope guys can help me with this annoying problem...
Always work overnight.


Regards,
matthew