Array Formula with 2 criteria

[Kellis]

Hi all,

I have an array formula where I am looking to return only 1 reference number if the number of incidents are equal to 4. In the spreadsheet the reference can appear multiple times, I have added a column to identify the first incidence of the reference number, not sure if I need this.

The array formula works to identify the number of incidents and area but returns duplicate reference numbers.

Please help!!

=IF($B$4:$B$17="Yes",INDEX($D$4:$D$17,SMALL(IF(COUNTIF(search_states,$C$4:$C$17)*COUNTIF(search_dates,$E$4:$E$17),(ROW($E$4:$E$17)-MIN(ROW($B$4:$B$17))+1),""),ROW(A1))))

As an Array

Please see file attached

 

[Somendra Misra]

Hi,

Why not make a pivot report with criteria in filter to get unique ref. no.

EDIT: Also see the attached file for new array formula with 1st apperance formula.

Regards,

 

[Kellis]

Thanks Somendra,

The array formula is great. A pivot table was not an option I had wanted on the main spreadsheet.

 

[bosco_yip]

2 criteria filter to return multiple matching values without duplicate non-array formula.

In F4, formula copied down :

=IFERROR(INDEX(D$4:D$17,MATCH(1,MMULT(ISNA(MATCH(D$4:D$17,F$3:F3,0))*(C$4:C$17=search_states)*(E$4:E$17=search_dates),1),0)),"")

Regards
Bosco