Index Function

[hoaianhthu]

Hello there,
Below is my data example and I would like your help to write a fomular so it can return 2 stores for each region with the lowest rank. Can you please help? I try the index, but it seems not working with if condition. I already tried the array formular so that it can rank by region. Thanks for all of your helps.

thanks
Vivian



Store Region amount rank by region
197 1 63072.99 1
301 1 53032.32 2
133 1 36863.81 3
069 1 34736.26 4
300 1 33810.79 5
227 2 37891.7 1
109 2 35472.65 2
213 2 34936.23 3
233 3 42065.95 1
226 3 40145.01 2
192 4 41993.38 1

 

[PaddyD]

hi - welcome to the board!

You could get all elaborate with your forumulas, but I'd just use a pivot table - because your ranking from 1 = high to 5 = low etc, you can use the 'show top n' feature & set it to 2, then get rid of the subtotals etc

Book1
	A	B	C	D	E	F	G	H	I	J
1	Store	Region	amount	rank		Sum of rank
2	197	1	63072.99	1		Region	Store	rank	Total
3	301	1	53032.32	2		1	69	4	4
4	133	1	36863.81	3			300	5	5
5	69	1	34736.26	4		2	109	2	2
6	300	1	33810.79	5			213	3	3
7	227	2	37891.7	1		3	226	2	2
8	109	2	35472.65	2			233	1	1
9	213	2	34936.23	3		4	192	1	1
10	233	3	42065.95	1
11	226	3	40145.01	2
12	192	4	41993.38	1
13
Sheet2

 

[hoaianhthu]

Thanks for your quickly respond, but is there any foumular for this instead of Pivot table?

thanks

 

[PaddyD]

as you asked...

Book1
	A	B	C	D	E	F	G	H	I
1	Store	Region	amount	rank		Region	Lowest	Next lowest
2	197	1	63072.99	1		1	300	69
3	301	1	53032.32	2		2	213	109
4	133	1	36863.81	3		3	226	233
5	69	1	34736.26	4		4	192	#NUM!
6	300	1	33810.79	5
7	227	2	37891.7	1
8	109	2	35472.65	2
9	213	2	34936.23	3
10	233	3	42065.95	1
11	226	3	40145.01	2
12	192	4	41993.38	1
13
Sheet2

formula is:

INDEX($A$1:$A$12,MAX(IF($B$1:$B$12=$F2,IF($D$1:$D$12=LARGE(IF($B$1:$B$12=$F2,$D$1:$D$12),COLUMN()-6),ROW($A$1:$A$12)))))

...array entered in g2 & copied across / down

 

[hoaianhthu]

It works perfect! Thanks you so much for your inputs! It is my first time to join the board and it is very helpful!

Vivian