Formula for First, second, third highest using index match

Caly

Board Regular
Joined
Jul 19, 2015
Messages
158
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hi is there a way to apply index match formula to show the areas that have the highest amount purchased and rank them by first,second,third?

Below is the data

AreaDallasTexasKansasCalifornia
ItemQuantity QuantityQuantityQuantity
Table10203040
Chair50607090
Books30206010
Food407052



I want to show for each product the ranking

For instance, for the first product I want a formula that will look at the data chart to pull out the first highest amount and then second and third

Table
First highest area - California
First highest quantity - 40


Second highest area. - Kansas
Second highest area quantity - 30

Third highest area - Texas
Third highest area quantity -20
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
How about something like this?

Book6
ABCDEFGH
1AreaDallasTexasKansasCalifornia
2ItemQuantityQuantityQuantityQuantityHighestSecondThird
3Table10203040CaliforniaKansasTexas
4Chair50607090CaliforniaKansasTexas
5Books30206010KansasDallasTexas
6Food407052TexasDallasKansas
Sheet4
Cell Formulas
RangeFormula
F3:H6F3=FILTER($B$1:$E$1,LARGE($B3:$E3,COLUMN()-5)=$B3:$E3)


Do you need the associated quantities as well?

Book6
ABCDEFGH
1AreaDallasTexasKansasCalifornia
2ItemQuantityQuantityQuantityQuantityHighestSecondThird
3Table10203040California (40)Kansas (30)Texas (20)
4Chair50607090California (90)Kansas (70)Texas (60)
5Books30206010Kansas (60)Dallas (30)Texas (20)
6Food407052Texas (70)Dallas (40)Kansas (5)
Sheet4
Cell Formulas
RangeFormula
F3:H6F3=FILTER($B$1:$E$1,LARGE($B3:$E3,COLUMN()-5)=$B3:$E3) & " (" & FILTER($B3:$E3,LARGE($B3:$E3,COLUMN()-5)=$B3:$E3)&")"
 
Upvote 0
Thank you. This worked great. I do have to include the sales for each area now. Is there a way to separate the quantity from the sales or show the same ranking that will show quantity and sales by area?

This is the chart


AreaDallasDallasTexasTexasKansasKansasCaliforniaCalifornia
ItemQuantity SalesQuantitySalesQuantitySalesQuantitySales
Table10122205003013040900
Chair50222607007054490800
Books30322206006092210700
Food404227080051002600
 
Upvote 0
Thank you. Sales were now added in as part of the criteria. The sales for each area by product are in columns next to the quantity. Is there a way to include in the formula for it to look at the Quantity separately and then look at the Sales?
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGHIJKLM
1AreaDallasDallasTexasTexasKansasKansasCaliforniaCalifornia
2ItemQuantitySalesQuantitySalesQuantitySalesQuantitySales
3Table10122205003013040900California40900
4Chair50222607007054490800California90800
5Books30322206006092210700Kansas60922
6Food404227080051002600Texas70800
Data
Cell Formulas
RangeFormula
K3:K6K3=FILTER($B$1:$I$1,(B3:I3=LARGE(B3:I3,COLUMNS($B$1:$I$1)/2+1)))
L3:M6L3=FILTER(B3:I3,ISNUMBER(MATCH($B$1:$I$1,K3,0)))
Dynamic array formulas.
 
Upvote 0
Solution
Terrific this worked great. Thank you
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top