Large Formula with Index Match by Criteria

[joleigh94]

Hi,

This is the first time I've posted here so apologies if any of this is incorrect.

I have a list of products ordered by week with their volume.

I would like to select the top ten products by week. I have found and used the formula which gets the top ten, but I am struggling to do it by week.

Formula I used to get top 10: =INDEX(C$2:$C$28,MATCH(1,INDEX(($D$2:$D$28=LARGE($D$2:$D$28,ROWS(I$2:I4)))*(COUNTIF(I$2:I4,$C$2:$C$28)=0),),0))

One of the ways I have tried to do it by category: =INDEX(C$2:$C$28,MATCH(1,($I$2=$A$2:$A$28)*(INDEX(($D$2:$D$28=LARGE($D$2:$D$28,ROWS(I$2:I2)))*(COUNTIF(I$2:I2,$C$2:$C$28)=0),)),0))

If this is not possible, I will have to do this in two steps by first creating a table of products by week, then selecting the largest.

Any help is greatly appreciated

Thanks,
Jo

 

[Fluff]

How about either of these

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	week		Ward	Population
2	202020		Sopwell	95					202020
3	202020		Ashley	70					Sopwell	Sopwell
4	202020		Watling	53					Ashley	Ashley
5	202020		Batchwood	41					Watling	Watling
6	202020		Ashley	31					Batchwood	Batchwood
7	202020		Verulam	24					Ashley	Ashley
8	202020		Ashley	21					Verulam	Verulam
9	202020		Welham Green and Hatfield South	21					Ashley	Ashley
10	202020		St Stephen	21					Welham Green and Hatfield South	Welham Green and Hatfield South
11	202020		Clarence	19					St Stephen	St Stephen
12	202020		Hatfield Central	15					Clarence	Clarence
13	202020		Sandridge	12
14	202020		Marshalswick North	12
15	202020		Wheathampstead	11
16	202020		Redbourn	8
17	202020		Redbourn	8
18	202020		Hatfield Central	7
19	202020		Park Street	6
20	202020		London Colney	4
21	202020		St Peters	3
22	202020		Hatfield Villages	2
23	202020		Colney Heath	1
24	202020		Wheathampstead	1
25	212020		Harpenden South	123
26	212020		Harpenden East	80
27	212020		Peartree	63
28	212020		Harpenden West	49
29	212020		Harpenden North	47
30	212020		Welwyn West	47
31	212020		Harpenden West	33
32	212020		Harpenden East	30
33	212020		Redbourn	14
34	212020		Welwyn West	9
35	212020		Welwyn West	2
Master

Cell Formulas
Range	Formula
I3:I12	I3	=FILTER(C2:C35,(A2:A35=I2)*(D2:D35>=AGGREGATE(14,6,D2:D35/(A2:A35=I2),10)))
J3:J19	J3	=IFERROR(INDEX($C$2:$C$35,AGGREGATE(15,6,(ROW($C$2:$C$35)-ROW($C$2)+1)/(($A$2:$A$35=$I$2)*($D$2:$D$35>=AGGREGATE(14,6,$D$2:$D$35/($A$2:$A$35=$I$2),10))),ROWS(I$3:I3))),"")
Dynamic array formulas.

 

[joleigh94]

Hi,

Thank you for getting back to me so quickly.

Unfortunately, I don't have the FILTER function. I tried to download it but I'm unable to without admin authorisation.

 

[joleigh94]

i just realised this is two separate suggestions.. i'll try the second one

 

[joleigh94]

that worked great - thank you so much! for some reason, one of the 17 weeks doesn't want to work

if you dont mind me asking, what does the AGGREGATE(15,6.... and AGGREGATE(14,6...... do/mean?

thank you so much again!

 

[Fluff]

This part
AGGREGATE(14,6,$D$2:$D$35/($A$2:$A$35=$I$2),10)
Is like the Large function & is finding the 10th largest value in col D where col A matches the value in I2.
And the Aggregate(15,6 is acting like the small function & is finding the 1st row, then 2nd row etc where col A matches I2 & col D is one of the 10 largest numbers.