Alpha Sort INDEX MATCH with Condition

[snt20147]

Hello! I want to use INDEX MATCH using Table 1 to create Table 2 that displays only "Winter" and sorts the month alphabetically. I used the following formula which did a great job of sorting by month, but I can't figure out how to get it to select only "Winter".

=INDEX(Month,MATCH(ROWS($C$2:C2),COUNTIF(Month,"<="&Month),0))

Thanks in advance for looking at my post!

Table 1
[TABLE="width: 268"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Rank[/TD]
[TD]ID[/TD]
[TD]Month[/TD]
[TD]Season[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]January[/TD]
[TD]Winter[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]234[/TD]
[TD]February[/TD]
[TD]Winter[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]345[/TD]
[TD]August[/TD]
[TD]Summer[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]456[/TD]
[TD]April[/TD]
[TD]Spring[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]567[/TD]
[TD]May[/TD]
[TD]Spring[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]678[/TD]
[TD]June[/TD]
[TD]Spring[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]102[/TD]
[TD]September[/TD]
[TD]Fall[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]789[/TD]
[TD]July[/TD]
[TD]Summer[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]901[/TD]
[TD]March[/TD]
[TD]Winter

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 226"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Table 2
Month[/TD]
[TD]

Season[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]Winter[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Winter[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]Winter[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

If you use helper columns I and J in my example, you can pull all winter records. Then you can use your formula on that to sort.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Rank	ID	Month	Season		month	Season		month	Season
2	1	123	January	Winter		February	Winter		January	Winter
3	2	234	February	Winter		January	Winter		February	Winter
4	3	345	August	Summer		March	Winter		March	Winter
5	1	456	April	Spring					#NUM!	#NUM!
6	2	567	May	Spring					#NUM!	#NUM!
7	3	678	June	Spring					#NUM!	#NUM!
8	3	102	September	Fall					#NUM!	#NUM!
9	1	789	July	Summer					#NUM!	#NUM!
10	2	901	March	Winter					#NUM!	#NUM!
Sheet1

Cell Formulas
Range	Formula
F2	{=IF(ROWS(F$2:F2)>COUNTIF($D$2:$D$10,"winter"),"",INDEX($I$2:$I$9,MATCH(ROWS($I$2:I2),COUNTIF($I$2:$I$9,"<="&$I$2:$I$9),0)))}
G2	{=IF(ROWS(F$2:F2)>COUNTIF($D$2:$D$10,"winter"),"",INDEX($J$2:$J$9,MATCH(ROWS($J$2:J2),COUNTIF($I$2:$I$9,"<="&$I$2:$I$9),0)))}
I2	{=IF(ROWS(F$2:F2)>COUNTIF($D$2:$D$10,"winter"),"",INDEX($C$2:$C$10,SMALL(IF($D$2:$D$10="winter",ROW($D$2:$D$10)-ROW($D$2)+1),ROWS($I$2:I2))))}
J2	{=INDEX($D$2:$D$10,SMALL(IF($D$2:$D$10="winter",ROW($D$2:$D$10)-ROW($D$2)+1),ROWS($I$2:J2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[XOR LX]

Hi,

=LOOKUP(1,0/FREQUENCY(ROWS(C$2:C2),COUNTIFS(Season,Season,Season,"Winter",Month,"<="&Month)),Month)

No need for CSE.

Regards