# Retrieving top 10 largest values depending on its corresponding column.

• I have attached a sample spreadsheet that has two tables(3rd is desired output), the second table is using the formula Index and match function to retrieve the top 10 largest product names based on the revenue value.

But if you could notice, for the SL no 4 and 5 the resultant product is P4, well technically P4 and P5 both must be displayed since it shares same revenue. How can I modify the formula to get desired output.

Any help is appreciated.

Thanks.

## Files

• What version of Excel do you have?

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Do you have the SORT function available to you?

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Hello,

Not sure to understand if you are after getting rid of all the ties in your ranking ...

See attached proposal

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Do you also have SORTBY?

If not, clear Q2:Q9, then enter this in Q2:

=INDEX(SORT(A2:B9,1,-1),0,1)

and it will spill down automatically. If you do have it, use:

=SORTBY(A2:A9,B2:B9,-1)

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Also one another modification.

I used the same formula to sort from smallest to largest(asc) by changing 0 to 1in Rank (see below).However It does not seem to work.

Code
1. =RANK(B2,\$B\$2:\$B9,1)+COUNTIF(\$B\$2:B2,B2)-1
• Hello,

Sorry .. but still do not understand what you are actually trying to achieve ...

IF your objective is to end up with no Ties ...

i.e. have a Tie Breaker ... in order to be able to Sort both in Ascending and in Descending order .....

Attached is a test file ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner

• Yes that was my objective. This is it.

Thank you so much Carim.

• Glad you could sort it out

Thanks for your Thanks ...AND for the Like

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner