How to return the header by value in a PivotTable

Question

I have a PivotTable like this:

Sum of Gf_Amount |  Column Labels               
                 |  2015    |        |        |         | Grand Total
Row Labels       |  17-Mar  | 18-Mar | 19-Mar | 20-Mar  |
3601             |  20      | 20     |        |         | 40
10386            |  35      |        |        |         | 35
76301            |  5       |        |        |         | 5
80941            |          |        |        | 10      | 10     
205738           |          |        | 5      |         | 5
219576           |          |  15    |        |         | 15
Grand Total      |  60      | 35     | 5      | 10      | 110

What I want do is find the last non-empty column and return the date according to the value. For example: for ID 3601 the result should be 2015 18-Mar.

Currently I know how to find the last non-empty column by using =LOOKUP(9.99E+307,B6:E6). For ID 3601 it gives me 20 which is correct. However when I use:

=INDEX($B$5:$E$5,MATCH(LOOKUP(9.99E+307,B6:E6),B6:E6,0))  

to find the header, it gives me 17-Mar which is the corresponding header for the first 20. Besides, the formula I wrote can't even give me the year.

Can anyone help me out so I can find the date and year? (It doesn't have to be in PivotTable. You can copy and paste it in a normal table.)

Answer 1

I'm guessing that your column labels are date indices formatted as dd-mmm so there is no need to find the 2015 that is displayed hence:

 =INDEX($5:$5,MATCH(1E+100,A6:E6))  

formatted as say dd-mmm-yyyy and presumably copied down may suit.

---

It is a peculiarity (perhaps never really intended) of the MATCH function that, without the optional argument, where it can’t find a match in a list it returns the index of the last entry in the list – very useful, as here, at times! So all the “big number” (there are lots of versions of it – for example the one you used 9.99E+307) does is feed MATCH a number so large it is never likely to find it (to force selection of the last entry).

I like 1E+100, a googol, as short and easy to remember, and for its ‘derivation’. 9.99E+307 is theoretically better as closer to the largest number Excel can handle: 9.99999999999999E+307 but

10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

for me is big enough – I don’t expect ever to want to work with a number bigger than that and smaller than or equal to 9.99E+307.