Hi Experts,

I have been struggling for a while now trying to solve a problem with standard Excel functions (LARGE, optionally with array functions)...so far no luck. I hope using VBA can solve this and also make it faster.

Data tables:
(@A15)
Freq Power
4 -40
10 -5
100 -3
180 -25
250 -5
270 -31
290 -27
295 -6
298 -9
300 -30

I need to derive the 4 largest powers and from this table
(@E15)
Rank Power Freq
1 -3 100
2 -5 10
3 -5 10 should be 250
4 -6 295

(@F16 and copied down) =LARGE($B$16:$B$25,E16)
(@G16 and copied down) =INDEX($A$16:$A$25,MATCH(F16,$B$16:$B$25,0))

This leads to 2 problems:
1) LARGE correctly returns duplicates (power of -5), but trying to find the corresponding freq based on only a value is not possible (the second instance should be 250 and not 10)
2) In the real situation I have much larger data tables and many more of them to analyze and this solution takes too long at the moment

I was looking for a solution in VBA (I have almost no experience in VBA programming) to create a LARGE-like function that returns the index of an array instead of the actual value. Then the standard excel formulas can easily take over.

Hope that someone can help