Explanation
Note: the lookup_value of 2 is deliberately larger than any values in the lookup_vector, following the concept of bignum .
Working from the inside out, the expression:
(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy"))
generates strings like “0117” using the values in column B and E, which are then compared to each other. The result is an array like this:
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
where TRUE represents dates in the same month and year. The number 1 is then divided by this array. The result is an array of either 1’s or divide by zero errors (#DIV/0!):
{1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
which goes into LOOKUP as the lookup array. LOOKUP assumes data is sorted in ascending order and always does an approximate match. When the lookup value of 2 can’t be found, LOOKUP will match the previous value, so lookup will match the last 1 in the array.
Finally, LOOKUP returns the corresponding value in result_vector, which contains the amounts in C5:C13.
Explanation
To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level.
In this case, we want the grand total of the “sales” field, so we simply provide the name the field in the first argument, and supply a reference to the pivot table in the second:
=GETPIVOTDATA("Sales",$B$4)
The pivot_table reference can be any cell in the pivot table, but by convention we use the upper left cell.
Note: GETPIVOTDATA will return the value field based on current “summarize by” settings (sum, count, average, etc.).
