RANK function - to return data based on 2 different columns

**Adsup01** · 01-29-2013, 05:08 PM

Hello,

I am trying to find a function to return the top 10 amounts (in cell) from a range...and return the top 10 in criteria as well as their corresponding rate code...in a separate column.

Column A Column B
BEDDING 10
PILLOWS 13
BLANKET 14
SHEETS 11
DUVET 7
MATTRESS 5

Etc....

Please see attached example.

Thank you VERY much

**FDibbins** · 01-29-2013, 05:15 PM

copy this down, it will give you the top 10...
=LARGE($D$5:$D$565,ROW(A1))
use this to extract the rate program...
=INDEX(B:B,MATCH(F5,D:D,0),1)

What to you want to do about duplicate matches?

**Adsup01** · 01-29-2013, 05:35 PM

Thank you FDibbins!

Thats a good question...is there a way to deliver duplicates?

Ex,...if there were 5 cells tied...to deliver all 5?

Thank you Again.

**FDibbins** · 01-29-2013, 05:53 PM

I added a helper column in F and used this, copied down...
=IF(D5=0,"",D5+COUNTIF($D$5:D5,D5)*0.1)

then changed the formula for top 10 to...
=INT(LARGE($F$5:$F$565,ROW(A1)))
and to pull the rate program...
=INDEX(B:B,MATCH(LARGE($F$5:$F$565,ROW(A1)),F:F,0),1)

**Adsup01** · 01-29-2013, 06:20 PM

This is GREAT!

Thank you FDibbins.

I appreciate your help.

**FDibbins** · 01-29-2013, 06:21 PM

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

**Adsup01** · 01-30-2013, 07:52 PM

Sorry to bother you. I am having some issues with the formula(s) you gave me to use.
Please see attached.
The duplicates are coming up with the same rate program. In other words if there are 3 rate programs with 8 rooms...ex."MOTB" is coming up for all 3 results.
Any help you can provide would be great.

Thank you