Lookups by getpivotdata are about 10 times as fast as regular index-match lookup, but best performance improvement is achieved by sorting your source data. The results do not change when the lookup function refers to a Data Table instead of a named range. With the index-match function on an unsorted list the number of lookups per seconds was: 500 (based on 20,000 lookups in 35 seconds) With the getpivotdata function using very flexible single argument string syntax (see here) the number of lookups per second was: 2,000 (based on 250,000 lookups in 145 seconds) With the getpivotdata function the number of lookups per second was: 6,000 (based on 250,000 lookups in 40 seconds) More info on how to implement sorted lookups in Excel can be found here and scroll down to section INDEX-MATCH in One Formula, Sorted Data With the index-match function on an sorted list the number of lookups per seconds was: 180,000!! (based on 1,440,000 lookups in 8 seconds). The results are (the fastest first and the slowest last): The lookup was done on a table with 241,000 records. I did a few performance tests on a dual core 2.33 GHz 2 GB RAM desktop PC with Excel 2007. If performance would be better, are there any reasons not to follow this approach? To be clear, there is no need to refresh the pivot table because it contains source data (which is located in the beginning of the calculation chain). Would you expect a performance improvement if I would use getpivotdata to extract and aggregate data instead? I would expect that within the underlying Pivot object aggregated values are pre-calculated and therefore performance would be better. To be able to use it, I first need to convert my large source table to a pivot table and after that I can extract data using the function getpivotdata. I recently learned about the existence of the function getpivotdata, which makes it possible to extract data from a pivot table.

To extract and aggregate data I usually use sumif, vlookup/hlookup and index+match functions. I need to extract data from this large table for my analysis. I build an Excel 2007 spreadsheet which contains a larger table with source data (about 500,000 rows and 10 columns).

Excel getpivotdata function true