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

Date

Category

Color

Result

Jan

Feb

Mar

1/1/2016

A

Red

A

Red, Grey

Blue, Brown

5/2/2016

A

Blue

B

Yellow

1/1/2016

B

Yellow

C

Red

5/3/2016

C

Red

D

13/1/2016

A

Grey

7/2/2016

A

Brown

<tbody>
</tbody>

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?