Look up for Big Data

Would you describe this as a "lookup" task, or a "filter" task? You have posted this in the VBA section of the forum -- how does VBA fit into the overall project (because my first thoughts don't involve VBA at all)?

If this is more of a "filter" type task, then look into using Autofilter (to filter the list in place) or Advanced filter if you need to extract the result(s) to another part of the spreadsheet.
Autofilter: https://www.contextures.com/xlautofilter01.html
advanced filter: http://www.excel-easy.com/examples/advanced-filter.html

If this is more of a lookup task, the most important thing you can do with a large data set is sort the database on the lookup value so you can use a binary search algorithm (approximate match options in Excel's built in lookup functions). Linear searches (Excel's lookup functions with exact match option selected) are extremely slow, so the most important thing you can do with a large data set is to make sure you can use a more efficient lookup algorithm like a binary search algorithm.

Either of these options can be programmed in VBA, but VBA is not necessary to execute these approaches.

How are you wanting to approach this problem? Can you generate a small representative sample file and upload it to the forum to illustrate exactly what you need to do?

That is starting to sound more like a Power Query aka Get and Transform type problem. I have no experience with these tools, so I can't really help. I found these tutorials: https://www.myonlinetraininghub.com/...pare-two-lists
https://www.howtoexcel.org/power-que...and-transform/
Perhaps someone more skilled at Power Query/Get and Transform can comment.

Without looking at sample source file and output, hard to say for sure. But I'd definitely try out PowerQuery.

You can easily merge, append different tables into single table to report on.

Typically, I'd set up Date dimension table using parameter. StDate to create list of dates with additional dimensions.
Then you can either perform aggregation based on dates and/or create relationship to combine data.

Ex: Paste below into blank query (using Advanced Editor). Then create StDate parameter with value set to Jan 1st of current year (i.e. 1/1/2018).

Please Login or Register  to view this content.