Lookup values and sort based on Month and Category

[Barcina]

Hi all,

I am trying to build a formula looking up text values and then sort them by Month and category
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Category[/TD]
[TD]Color[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]1/1/2016[/TD]
[TD]A[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Red, Grey[/TD]
[TD]Blue, Brown[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/2/2016[/TD]
[TD]A[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2016[/TD]
[TD]B[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]5/3/2016[/TD]
[TD]C[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/1/2016[/TD]
[TD]A[/TD]
[TD]Grey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/2/2016[/TD]
[TD]A[/TD]
[TD]Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ideally, it should also skip duplicates e.g. if you have twice category A with color red in January it should list red just once. I tried different formulas from similar questions but just can't figure it out and would be very happy if you had any suggestions or advice.

Thank you

 

[Barcina]

Hi again,

I have this formula for the problem described above:
{=IF(ISERROR(INDEX($C$4:$E$172,SMALL(IF($D$4:$D$172=$G$4,ROW($D$4:$D$172)),ROW(1:1))-3,3)),"",INDEX($C$4:$E$172,SMALL(IF($D$4:$D$172=$G$4,ROW($D$4:$D$172)),ROW(1:1))-3,3))}

but as soon as I add the date condition it stops working:
{=IF(ISERROR(INDEX($C$4:$E$172,SMALL(IF(AND($D$4:$D$172=$G$4,B4<=EOMONTH($F$20,0),B4>=EDATE($G$21,0)),ROW($D$4:$D$172)),ROW(1:1))-3,3)),"",INDEX($C$4:$E$172,SMALL(IF(AND($D$4:$D$172=$G$4,B4<=EOMONTH($F$20,0),B4>=EDATE($F$21,0)),ROW(1:1))-3,3)))}

Any ideas why?