Lookup a cell with multiple identifiers

[Staggl]

This is a bit hard to explain, but I'll do my best. I have a pivot table with broad categories broken down into sub-categories with data out to the right. I need to pull the subcategory name that corresponds to the broad category and a subcategory of rankings i.e. 1 2 3 4 5 etc...

The tricky part is that the rankings start over per each broad category. For example:

Asset Class Category Rankings
Allocation Moderate Allocation 2
World Allocation 1
Conservative Allocation 3
Alternatives Real Estate 2
Managed Futures 1

etc...

So I would need to pull the #1 Ranking of the broad Asset Class (if Allocation then 'World Allocation'). This will change month to month. I also would prefer not to have to sort the data before i link it to a formula b/c there are multiple rankings of multiple statistics in the table. Any ideas?

I do appreciate any assistance. Please let me know if I need to explain something further.

 

[Staggl]

Sorry format screwed up.

'Asset Class' 'Category' and 'Rankings' are the headings. 'Allocation' falls under Asset Class. 'Moderate Allocation' 'World Allocation' and 'Conservative Allocation' are subcategories of Allocation. Real Estate and Managed Futures are subcategories of Alternatives, which falls under Asset Class. The rankings are per asset class (they start over each asset class).