Speeding up a lookup task on a large database

Performing calculations or writing formulas on large databases pose the following major problems:

1. Time taken to process is unduly long; and
2. File size increases disproportionately

A typical problem is fetching data from another worksheet or workbook via the VLOOKUP() function.  Quite often, when one confirms the VLOOKUP() formula in a single cell with the Enter key, then at the bottom right of your MS Excel screen (the task bar), a white progress bar appears which reads “Calculating (4 processors) 1%”.  Once has to wait for the processing to complete before copying the formula down.  The process of copying is obviously just as time consuming and resource intensive.

In these situations, a workaround is the JOIN the tables in MS Access and then get the data back in MS Excel.  For accomplishing this, one must have at least some working knowledge of “SQL Query” writing.

If you are using the Power Query add-in for MS Excel 2010 and higher versions, then one can fetch data from another worksheet or workbook without writing a single formula.  Furthermore, there is absolutely no lag in generating the result.

You may refer to my Power Query solution in this workbook (This is a 20 MB file)

You may watch a short video of my solution here