# Avoiding commas in formula if no value exists

#### Challis

##### New Member
Hi guys,
I have this formula which matches two criteria and returns upto 4 values all in the same cell.
It works well and as i intended but one thing I'd like to address is only putting commas in if a trailing value is returned.
So my question is, if no value is returned, is there a way to not show the trailing comma.
So say for example if one value was returned "A" it would just show "A". If two values were returned "A" & "B"" it would show "A,B".

Code:
``=IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!\$P\$6:\$P\$1000=Dashboard!\$A3)*(Schedule!\$S\$6:\$S\$1000={"Inspecting","Authoring Report"}),ROW(Schedule!\$C\$6:\$C\$1000)),ROWS(C\$2:C\$2))),"")&", "&IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!\$P\$6:\$P\$1000=Dashboard!\$A3)*(Schedule!\$S\$6:\$S\$1000={"Inspecting","Authoring Report"}),ROW(Schedule!\$C\$6:\$C\$1000)),ROWS(C\$2:C\$3))),"")&", "&IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!\$P\$6:\$P\$1000=Dashboard!\$A3)*(Schedule!\$S\$6:\$S\$1000={"Inspecting","Authoring Report"}),ROW(Schedule!\$C\$6:\$C\$1000)),ROWS(C\$2:C\$4))),"")&", "&IFERROR(INDEX(Schedule!C:C,SMALL(IF((Schedule!\$P\$6:\$P\$1000=Dashboard!\$A3)*(Schedule!\$S\$6:\$S\$1000={"Inspecting","Authoring Report"}),ROW(Schedule!\$C\$6:\$C\$1000)),ROWS(C\$2:C\$5))),"")``
Thanks

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### jtakw

##### Well-known Member
Hi,

Can you show various sample results from the formula that contain the unwanted , commas?

#### Marcelo Branco

##### MrExcel MVP
Maybe...(untested)
Use a space as separator rather than ", " and try
=SUBSTITUTE(TRIM(formula here)," ",", ")

Hope this helps

M.

Last edited:

#### Marcelo Branco

##### MrExcel MVP
The above formula doesn't work properly with the values returned by the INDEX functions contain spaces.
Maybe something like this
=SUBSTITUTE(IFERROR(", "&INDEX(.....),"")&IFERROR(", "&INDEX(.....),"")&IFERROR(", "&INDEX(.....),"")&IFERROR(", "&INDEX(.....),""),", ","",1)

M.