VLOOKUP or INDEX MATCH doesn't update columns when referencing pivot table

Question

I'm going bonkers trying to figure this out. I thought I hit the jackpot when I learned about INDEX-MATCH and the additional flexibility over VLOOKUP, but that flexibility doesn't seem to help when the formula references a pivot table.

My problem is that I need to add another column to the pivot table, but when I do, all of the VLOOKUP formulas referencing data to the right of the new column are botched. I can get it to automagically update the column letter in the formula only when referencing a regular table.

If the VLOOKUP references data from a pivot table that I add a column to... disaster strikes...

Solution

GETPIVOTDATA is the native lookup function for pivot tables so you can use this instead of VLOOKUP or INDEX and MATCH. The strings in the GETPIVOTDATA function can be assigned to your variables for an effective lookup.

Comments

[CallumDA]

=GETPIVOTDATA() is the native lookup function for pivot tables.

[Boris]

DUH! That works well. I was able to hit = and then just click on the calculated result from the pivot table and "presto". In order to downfill, I had to adjust the formula from the actual customer name to a cell number that would reference the customer name; like so: ("Customer Name", "Acme") to ("Customer Name", L10). Then, I was able to fill down and get a list of grand totals that will adjust automagically when I add/remove columns from the pivot table. Thanks for the assist!

[CallumDA]

Pleased you found your solution!

[Excellll]

@CallumDS33 Please post your solution as an answer rather than as a comment. Comments are impermanent on this site; answers in comments can be lost over time due to site housekeeping practices. Posting as an answer will also make it easier for people with similar problems to recognize the question has been answered. Oh, and rep.