Formula for First, second, third highest using index match

[Caly]

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

Area	Dallas	Texas	Kansas	California
Item	Quantity	Quantity	Quantity	Quantity
Table	10	20	30	40
Chair	50	60	70	90
Books	30	20	60	10
Food	40	70	5	2

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

 

[kweaver]

How about something like this?

Book6
	A	B	C	D	E	F	G	H
1	Area	Dallas	Texas	Kansas	California
2	Item	Quantity	Quantity	Quantity	Quantity	Highest	Second	Third
3	Table	10	20	30	40	California	Kansas	Texas
4	Chair	50	60	70	90	California	Kansas	Texas
5	Books	30	20	60	10	Kansas	Dallas	Texas
6	Food	40	70	5	2	Texas	Dallas	Kansas
Sheet4

Cell Formulas
Range	Formula
F3:H6	F3	=FILTER($B$1:$E$1,LARGE($B3:$E3,COLUMN()-5)=$B3:$E3)

Do you need the associated quantities as well?

Book6
	A	B	C	D	E	F	G	H
1	Area	Dallas	Texas	Kansas	California
2	Item	Quantity	Quantity	Quantity	Quantity	Highest	Second	Third
3	Table	10	20	30	40	California (40)	Kansas (30)	Texas (20)
4	Chair	50	60	70	90	California (90)	Kansas (70)	Texas (60)
5	Books	30	20	60	10	Kansas (60)	Dallas (30)	Texas (20)
6	Food	40	70	5	2	Texas (70)	Dallas (40)	Kansas (5)
Sheet4

Cell Formulas
Range	Formula
F3:H6	F3	=FILTER($B$1:$E$1,LARGE($B3:$E3,COLUMN()-5)=$B3:$E3) & " (" & FILTER($B3:$E3,LARGE($B3:$E3,COLUMN()-5)=$B3:$E3)&")"

 

[Caly]

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

Area	Dallas	Dallas	Texas	Texas	Kansas	Kansas	California	California
Item	Quantity	Sales	Quantity	Sales	Quantity	Sales	Quantity	Sales
Table	10	122	20	500	30	130	40	900
Chair	50	222	60	700	70	544	90	800
Books	30	322	20	600	60	922	10	700
Food	40	422	70	800	5	100	2	600

 

[kweaver]

Not quite sure what you now want.

 

[Caly]

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?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Area	Dallas	Dallas	Texas	Texas	Kansas	Kansas	California	California
2	Item	Quantity	Sales	Quantity	Sales	Quantity	Sales	Quantity	Sales
3	Table	10	122	20	500	30	130	40	900		California	40	900
4	Chair	50	222	60	700	70	544	90	800		California	90	800
5	Books	30	322	20	600	60	922	10	700		Kansas	60	922
6	Food	40	422	70	800	5	100	2	600		Texas	70	800
Data

Cell Formulas
Range	Formula
K3:K6	K3	=FILTER($B$1:$I$1,(B3:I3=LARGE(B3:I3,COLUMNS($B$1:$I$1)/2+1)))
L3:M6	L3	=FILTER(B3:I3,ISNUMBER(MATCH($B$1:$I$1,K3,0)))
Dynamic array formulas.

 

[Caly]

Terrific this worked great. Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.