=JOIN formula with two COLLECT statements

Benjamin Brunnette

Hey Community,

I'm running into a roadblock with a formula that I thought was correct, but its coming up with the error #INVALID DATA TYPE.

Here is the formula I'm using in reference to the pictured cells attached:

=JOIN(AND(COLLECT([TOPIC2]:[TOPIC2], [TITLE2]:[TITLE2], TITLE@row), COLLECT([TOPIC2]:[TOPIC2], [DATE2]:[DATE2], DATE@row)), CHAR(10))

Essentially, here's what I want:

IF the team name in the [TITLE2] column equals the team name in the [TITLE]@row cell

AND the date in the [DATE2] column equals the date in the [DATE]@row cell

COLLECT all values in the [TOPIC2] column that meet this above criteria and put all those values in the [TOPIC]@row cell

Thank you for any help!!

Leibel S

@Benjamin Brunnette

See below formula. This will collect all TOPIC2 where TITLE2 matches TITLE@row and DATE2 matches DATE@row.

=JOIN(COLLECT([TOPIC2]:[TOPIC2], [TITLE2]:[TITLE2], TITLE@row,[DATE2]:[DATE2], DATE@row), CHAR(10))

The collect formula you can just keep on adding all the criteria that you want. No need for an AND statement.

Leibel S

@Benjamin Brunnette

See below formula. This will collect all TOPIC2 where TITLE2 matches TITLE@row and DATE2 matches DATE@row.

=JOIN(COLLECT([TOPIC2]:[TOPIC2], [TITLE2]:[TITLE2], TITLE@row,[DATE2]:[DATE2], DATE@row), CHAR(10))

The collect formula you can just keep on adding all the criteria that you want. No need for an AND statement.

Benjamin Brunnette

@Leibel S - this worked perfectly. Thank you so much!