Rank for Top 5 after two criteria match excel formula

[amartakale]

rank.xlsx

[amartakale]

Here I want top 5 companies rank wise after criteria match between year

[p45cal]

Array-Enter in cell J6:

=INDEX($C$4:$C$204,MATCH(LARGE(IF($B$4:$B$204=$J$3,INDEX($D$4:$F$204,,MATCH($K$3,$D$3:$F$3,0))),I6),INDEX($D$4:$F$204,,MATCH($K$3,$D$3:$F$3,0)),0))

Array-Entering which means committing the formula to the sheet usingh Ctrl+Shift+Enter, NOT just Enter.
Then copy down five cells.

[amartakale]

This is fabulous work! Perfect,You are done it Sir………

I just made some tests and it works perfectly and does exactly what I need it to, thanks so much Sir and God bless you!

[amartakale]

Dear Sir

I use sum product formula to extract sales,PAT, Weig. Numbers according matching year & company & its also work perfectly also (Just delete row 56 & check) but if formula found same companies in range then it calculate as SUM & I dont want this then how to modified this formula. can it possible? Pls check sheet.
rank1.xlsx

[p45cal]

Ah, I'd assumed only one instance of Co./Year combination would appear.
In your most recent attachment try in cell L6:

=INDEX(INDEX($D$4:$F$205,,MATCH($L$3,$D$3:$F$3,0)),MATCH(LARGE(IF($B$4:$B$205=$K$3,INDEX($D$4:$F$205,,MATCH($L$3,$D$3:$F$3,0))),J6),INDEX($D$4:$F$205,,MATCH($L$3,$D$3:$F$3,0)),0))

This is a quick fix as I'm out of time; I suspect there'll be a shorter version (it's just a rehash of the formula in cell K6).

[amartakale]

Excellent Sir,you can done it.Thanks very much.....

[amartakale]

Dear Sir

Sorry for you disturb

with help of your formula I am extracting

1) Top Ten Companies in specific Year after Criteria match
2) Top Ten Sector in specific Year after Criteria match
3) Now I want: Top Ten Companies in specific Year after 2 Criteria match like field name PAT & sector name Automobile.
4) after this goal completed I want one more criteria match with all above like a Index (cell T3,Data in Column A) but it is not very important.I know it is very completed formula where I am unsuccessful to do it. If I will do this then my goal complete in all way.

If I will compete it with all criteria match then my boss will do like

Thanks in advance & hats off you SirNSE Rank+2.xlsx

[amartakale]

Any Update Sir

[p45cal]

after this goal completed I want one more criteria match with all above like a Index (cell T3,Data in Column A) but it is not very important.I know it is very completed formula where I am unsuccessful to do it. If I will do this then my goal complete in all way

This is too much work.
Attached is a pivot tables solution. 3 sets of 3 pivot tables. Much easier. Each pivot shows the top ten and sorted large to small.
To stop the problem in msg#5 above I've had to add something to make each row of the source data unique, so in column A I've added an index (great if that unique index already exists (nifty index??), use that instead of my unique numbers).
Move the pivot tables around to suit, even putting them on different sheets?

[amartakale]

Dear Sir,

Great!

Thank you very much for all of your efforts on this. I sincerely appreciate it. I know this is a very big stuff. I use Excel 2010 & I required only formula if possible I am also trying on that.

Again thank you so much, your assistance is greatly appreciated.

Best regards and wishing you a fine day.
Amar