Dear TM,

the formula is working well, but only the result the FIRST match, due
ROWS($1:1).
but intentionally, we would like to concatenate ALL date available in one
cells.

if we copy the formula to other column, it workable.

here is the tested formula:

TGL Man Region
16-Aug-08 A N B E
17-Aug-11 B E
7-Sep-09 C S
1-Feb-10 B E
15-Apr-07 B E
4-Sep-07 D N
4-May-07 E N
6-Sep-07 A N
3-Oct-10 B E
24-Sep-08 E E


15-Apr-07 1-Feb-10 3-Oct-10 17-Aug-11
=IF(ISERR(MATCH(SMALL(IF((MAN=$T21)*(Region=$U21), COUNTIF(TGL,"<"&TGL)),COLUMN(Q34)-16),COUNTIF(TGL,"<"&TGL),0)),"",INDEX(TGL,MATCH(SM ALL(IF((MAN=$T21)*(Region=$U21),COUNTIF(TGL,"<"&TG L)),COLUMN(Q34)-16),COUNTIF(TGL,"<"&TGL),0)))


"Teethless mama" wrote:

Try this:

=IF(ISERR(MATCH(SMALL(IF((Salesman="B")*(Region="E "),COUNTIF(Date,"<"&Date)),ROWS($1:1)),COUNTIF(Dat e,"<"&Date),0)),"",INDEX(Date,MATCH(SMALL(IF((Sale sman="B")*(Region="E"),COUNTIF(Date,"<"&Date)),ROW S($1:1)),COUNTIF(Date,"<"&Date),0)))


ctrl+shift+enter, not just enter
copy down as far as needed



"Andri" wrote:

Dear All,

Here is the sample database.

Date Salesman Region
16-Aug-08 A N
16-Aug-09 B E
16-June-07 C S
15-Aug-07 A S
15-Apr-07 B E
4-Sep-07 D N
4-May-07 E N
6-Sep-07 A N
3-Oct-07 B W
24-Sep-07 E E

i would like to concatenate for Salesman B and Region E(east) in A1, so the
result will be "15-Apr-07,16-Aug-09"... which formula can solve it?

tried SMALL + sumproduct(but...this sum all the date in that arrays).

thank you for your kind attention and help.

respectfully,
andri