How to do an index match on a pivot table?

[rubin1116]

Hi, I have a large set of transactional data that I put into a pivot to figure out the sum of the number of tickets sold for each event. In a different tab, I need to match the number of tickets sold to the event date. (I.e. in the example attached, I need to match the number of tickets sold to the New York X game on 4/4/21(B37) from the Pivot table to number of tickets sold for 4/4/21 on the New York X tab (L4). I can't figure out how to do this. I greatly appreciate anyones help!

I will eventually have to do this for multiple teams (i.e. multiple tabs for each team) but for this example I am just including the Yankees. Please let me know.

 

[vletm]

rubin1116
Do You use Pivot-table or Power Pivot?
if Pivot-table then continue reading ... otherwise ... skip this.

Some hints/ideas:
a) You file seems to use 'different' dates - that could make some challenges ...
... left side side seems to have mm/dd/yy and right side dd/mm/yyyy
b) to 'match' dates You could use 'Repeat All Item Labels'-option with Tabular Form

 

[p45cal]

Edit post posting: just realised this is more then 6 weeks old

In L4 of the New York X sheet:
=GETPIVOTDATA("# Tickets",Pivot!$A$3,"start_date",DATE(2000+RIGHT(B4,2),LEFT(B4,2),MID(B4,4,2))+D4,"vendor","New York X")
You can copy this formula up and down in the column. This formula is more complex because your dates in column B are just strings so:
DATE(2000+RIGHT(B4,2),LEFT(B4,2),MID(B4,4,2))
converts them to real dates. There's also the +D4 because your pivot table is made from data which has a time element too (I've changed the format of the dates in the pivot to include times just to show that). Column D contains time data too, and it mostly corresponds to the times in your pivot, but not all of the data which feeds your pivot table has a time element (18th April, 6th May, 23rd May). In the New York X sheet I've highlighted 3 cells with time data in which do NOT correspond with the time data in the pivot. Delete the contents of those cells and you'll see valid data appearing in column L for those rows.

These time elements of the data seem to be messing things up; do you really need them (will you need to differentiate multiple events on the same date at the same location)?

To make things more robust and simpler I would:
1. Convert those date strings in column B to real Excel dates.
2. Add a column in your pivot's source data to take out the time element, and use that column in your pivot instead of start_date.

In the file attached called Chandoo45997Client Capcacity Analysis v2.xlsx there is no tampering of data and that long GETPIVOTDATA formula.
In the other file called Chandoo45997Client Capcacity Analysis v2B.xlsx I have converted column B to real dates, added a new Source data sheet (Sheet7) for the pivot table wheree I've added a new column Q headed DateOnly which I've used in the Pivot. Now the formula in L4 is a much shorter
=GETPIVOTDATA("# Tickets",Pivot!$A$3,"vendor","New York X","DateOnly",B4)
without the need to worry about time.

In both files, were you see #REF! it's only because the data isn't present for that date and vendor in the pivot table. You can hide those errors with the likes of:
=IFERROR(GETPIVOTDATA("# Tickets",Pivot!$A$3,"vendor","New York X","DateOnly",B4),"")