Dear Experts, Please help me to get Top 5 sales with regions in D2:E6

If we insert a column in column D to show the rank, and list out 1-5, we can get this, which I think is what you want: Here is the formula for cell F2: =LARGE(B$2:B$11,D2) and the formula for cell E2: =INDEX(A$2:A$11,MATCH(F2,B$2:B$11,0)) Then, just copy E2:F2 all the way down to row 6. Note that think could get a little hairy if the Sales values are not unique, i.e. you have two or more records with the exact same sales.

You are taking the 1st 5 rows of the data & then sorting it, you need to sort the data & then take the 1st 5 rows.

Question for learning purposes Using the data in post #4 (Peters response) When this formula is applied =VSTACK({"Rank","Region","Sales"},HSTACK(SEQUENCE(5,1,1,1),TAKE(SORTBY(A2:B11,B2:B11,-1),5,2))) it gives the below RankRegionSales 1D987 2E888 3F888 4C770 5B456 What my question is when I use the formula =LARGE(TAKE($B$2:$B$11,5),ROWS($I$15:I15)) I get the below, why does it ignore the second 888 what is the TAKE in first formula doing to include both 888's but my TAKE formula excludes one of the 888's 987 888 770 456 230 Many thanks as always

Formula to get Top 5 sales detail

Arts

Well-known Member

Jan 23, 2025

Thanks Fluff! I just assumed the LARGE would go over the array and extract the five highest values, didn't think they needed to be sorted first.

Upvote 0

Formula to get Top 5 sales detail

Zubair

Active Member

Arts

Well-known Member

Excel Facts

Similar threads

Forum statistics

Share this page

Formula to get Top 5 sales detail

Zubair

Active Member

Arts

Well-known Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock