Rank multiple criteria, not repeating

Dear All,

​I have a rank formula that allows me to extract top 10 data, but if the data is less than 10, the rank repeats its self.

=INDEX(C6:C1048576,MATCH(LARGE(D6:D1048576,ROW(A1:A10)),D6:D1048576,0),1)

I have been trying to upload the excel sheet, I always get an error. hope the picture will help

i have attached the excel sheet for reference, hope someone can help improve my current formula. thank you.

Francis






francis

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Are you still using Excel 2016?
If you want to check your current version, please click in Excel on your account name (top) and click office user info

Please update your forum profile NOW and provide a sample workbook as requested.

Clear G3 to K13 entirely, then try this in K3:

=LET(r,SEQUENCE(10,,1,1),f,FILTER(B6:E14,D6:D14>=(LARGE(D6:D14,MIN(10,COUNT(D6:D14)))),""),s,SORTBY(f,INDEX(f,,3),-1),IFNA(HSTACK(r,s),""))

Actually, SEQUENCE won't work for you, so clear only H3 to K13 and try this in H3:

=LET(f,FILTER(B6:E14000,(D6:D14000>=(LARGE(D6:D14000,MIN(10,COUNT(D6:D14000)))))*(D6:D14000<>""),""),s,SORTBY(f,INDEX(f,,3),-1),IFNA(s,""))

Change ranges to suit.

According Microsoft SEQUENCE should be available in Excel 2021 and HSTACK should not be available in Excel 2021.

Users Excel 2021, please empty all cells in H4:K13 and try in H4:

Formula:

Please Login or Register  to view this content.

Yes, you are correct, however there is no HSTACK in my amended formula.

Thanks for the rep.

You are Welcome!
Thanks for your feedback, Glad to have helped. .

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.