Using IFERROR with a Join Formula

Todd Smelser

I have a JOIN formula that combines four cells

=JOIN([Add Request Type]@row:[Entitlement]@row, ", ")

I am wondering if I can get help to create an IFERROR formula that would leave a cell in the JOIN Column blank if one of the cells contained in the JOIN criteria is blank.

Andrée Starå

@TSmelser

Try this one.

=IF(COUNTIF([Add Request Type]@row:Entitlement@row, "") > 0, "", JOIN(COLLECT([Add Request Type]@row:Entitlement@row, [Add Request Type]@row:Entitlement@row, <>""), ","))

Did that work?

Bassam Khalil

Hi @TSmelser 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Todd Smelser

Hi,

So the first screen shot shows the 4 columns that are contained in the JOIN formula that populates my Index Match Column (The column is named wrong but is in several formulas so I don't change it)

The second row down has the fields that I want to return a result in my Index Match (JOIN) column. The third and forth rows are incomplete and I would like the IFERROR formula to leave the cell blank instead of the non relevant data "Australia East" in this screen shot

What I do not want is the "," and "Australia East" to be displayed if one of the 4 columns mentioned above is blank.

Thanks, always, for your help.

Andrée Starå

Hi @TSmelser

I hope you're well and safe!

Try something like this.

=JOIN(COLLECT([Add Request Type]@row:[Entitlement]@row, [Add Request Type]@row:[Entitlement]@row, <>""), ",")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Todd Smelser

Hi, That does eliminate the "," from the Column but it does not leave the cell in the Column blank

Andrée Starå

@TSmelser

Not sure I follow.

Can you elaborate?

Bassam Khalil

@TSmelser

If you want to show blank cell only when any of the 4 column is empty please try the following:

=if(count([Add Request Type]@row:[Entitlement]@row<4,"",JOIN([Add Request Type]@row:[Entitlement]@row, ", ")))

Todd Smelser

Hi,

That one returns INCORRECT ARGUMENT SET

Todd Smelser

Hi Andre,

Great to hear from you. The formula you provide Makes my current result, ", Australia East, " into "Australia East". No Commas.

Cleaner, but still not what I need.

Andrée Starå

@TSmelser

Likewise!

What do you need more specifically?

Todd Smelser

Hi Andre,

So, if one of the 4 criteria in the 4 cells I have included in my JOIN formula is blank, I would like the cell that contains the JOIN formula to be completely blank.

So, JOIN([COLUMN 1]@Row: [COLUMN 4]@Row) = COLUMN 1, Column 2, Column 3, Column 4 (assuming values are present in all 4 cells)

Should one of the cells in any of those columns be blank, the Cell in the JOIN Column should also be blank.

Andrée Starå

@TSmelser

Try this one.

=IF(COUNTIF([Add Request Type]@row:Entitlement@row, "") > 0, "", JOIN(COLLECT([Add Request Type]@row:Entitlement@row, [Add Request Type]@row:Entitlement@row, <>""), ","))

Did that work?

Todd Smelser

I think this will work. Thank you!

Andrée Starå

@TSmelser

Excellent!

You're more than welcome!