Ranking Values with a nested Index+Match

[MaxLut]

I have a sheet with economic indicators in column A, countries in B, and annual values in C-Z, and in AA I have the average annual value for that indicator.

Indicators repeat for each country, and countries repeat for each indicator, so it looks like:

Exports....US
Exports....China
Imports....US
Imports....China

I'm making a condensed sheet with each indicator in column A (indicators not repeating this time), and rank in the rows (B1=1,C1=2 etc.)

I'm part-way there. I've got a formula that will return the name of the country with the highest average value for each economic indicator, but I can't figure out how to rank them instead of just finding the maximum(switching MAX with rank.avg or rank.eq gives me #N/A even though it's an array formula).

Here is my formula thus far:

{=INDEX(Sheet8!$B:$B,MATCH(MAX(INDEX(Sheet8!$AA:$AA,MATCH(1,IF(Sheet8!$A:$A=Sheet1!$A2,1)))),Sheet8!$AA:$AA))}

^^This returns the correct answer that the United States has the highest average value for that indicator^^

Also, I'm not sure if this formula is matching each indicator's maximum value, or if it's just matching that specific value's first occurrence in the entire sheet. Since my data is continuous (e.g. 128,833.35462.....), the values only occur once, but in the future if I were using discrete data (e.g. 1,2,3...), maybe this formula won't work?

Thanks for reading through all of this, I appreciate any input!

 

[C Moore]

The Large function should be able to handle what you want, it will just be a matter of figuring out how to set k.