Ensuring high performance |
To ensure the best possible updating (recalculation) performance for very large worksheets (containing millions of cells and large numbers of formulas), you should follow a few rules:
If you use the vlookup(), match() functions for large data sets, always do one the following:
For the binary searching mode the performance gain increases significantly with the number of cells. Plain vlookup()/match() use the slow, sequential mode
require on average N/2 steps to find a value in an N-element range. In the binary mode only log2(N) steps can be required - for 10 million rows its over 200000 times less.
As the total execution time for lookup functions depends also on other pre- and post-searching operations, the real-life performance gains
will be most spectacular for large ranges or for updating large numbers of such functions.