Hello all


We´re using a pivot table with some complex formulas to crunch some extensive data, and we need to extract some data directly from the pivot table for further analysis.

This is the original pivot table:
Original Pivot table.JPG

And this is the lookup table with the needed values:
Lookups.JPG


Example => AA:

- Current Zone Nr = 6
(Last price for AA is 27.48, which is within 28.22 (Zone 6 High) and 27.08 (Zone 6 Low), so the Current Zone Nr is 6)

- Current Zone High = 28.22
- Current Zone Low = 27.08
- Current Zone Rank = 12

- Prev Zone Rank = 11
(If the current zone is nr 6, return the Zone Rank from Zone nr 5)

- Next Zone Rank = 9
(If the current zone is nr 6, return the Zone Rank from Zone nr 7)


Since it´s a powerpivot we could alter the layout to make it easier for the lookups, like showing the symbols in all rows.


How can we do it?

Here is the sample file:
http://www.mediafire.com/file/72e9cq...ps+sample.xlsx