Excel lookup data from google docs spread sheet

I did a few performance tests on a dual core 2.33 GHz 2 GB RAM desktop PC with Excel 2007.

The lookup was done on a table with 241,000 records. The results are (the fastest first and the slowest last):

1. 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). 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

2. With the getpivotdata function the number of lookups per second was:6,000 (based on 250,000 lookups in 40 seconds)

3. 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)

4. 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)

The results do not change when the lookup function refers to a Data Table instead of a named range.

So to answer the question. 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. Sorting your source data could make your lookup 400 times as fast.