Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Archive for September 20th, 2016

Using Match and Index on a Pivot Table: Sometimes Excel has better Performance with “Old School” Formulas

without comments

I am a huge fan of pivot tables, and often use them to help transform raw data into a different table, by “mirroring” data from a pivot table, where I have a table to the left of the pivot table, and I want to have the table show the same value the pivot table has, or possibly transform it with a formula.  Often you can just choose the cell reference that the pivot table has and you are done…  But sometimes the pivot table may have multiple columns, that vary, and you wish to do some sort of lookup on the pivot table…  Traditionally, to do this, it would require creating a GETPIVOTDATA formula.  These have always been a bit complex, and now with PowerPivot being built-in to Excel 2016, they are even MORE complex.  This is why some people choose to use cube functions instead, which can now be done on PowerPivot tables….  But I have tested both of these, and they are very slow when you have a lot of rows.   So, I have gone back to an old school method of using the MATCH function (with absolute references) to determine which column a particular heading is in, and then use the INDEX function to select the value from that heading.  This old school trick turns out to be a LOT more efficient than using GETPIVOTDATA, and thus I thought I should share this with any other Excel nerds who read my blog.

Written by Jacob Walker

September 20th, 2016 at 6:59 pm