Alternative to textjoin for this function to work

[Jfawcett]

I have the following function which works great on my laptop with the updated version of excel, however my computer at work has an older version and textjoin results in a NAME error.

These are the functions

=TEXTJOIN(", ",,IFERROR(SMALL(IF(E5:AA5=0,$E$1:$AA$1),{1;2;3}),""))
and
=TEXTJOIN(", ",,IFERROR(INDEX($E$2:$AA$2,N(IF(1,SMALL(IF(E5:AA5=0,$E$1:$AA$1),{1,2,3})))),""))

Thanks for your help and any suggestions

 

[jasonb75]

There is no alternative in older versions of excel, the best that you could do would be to use a UDF (custom function written in vba). For example

Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    ' With a minor edit to ignore blanks
Dim y As Variant
If TypeOf a Is Range Then
    For Each y In a.Cells
        If Len(y.Value) > 0 Then aconcat = aconcat & y.value & sep
    Next y
ElseIf IsArray(a) Then
    For Each y In a
        If Len(y) > 0 Then aconcat = aconcat & y & sep
    Next y
Else
    aconcat = aconcat & a & sep
End If
    If Len(aconcat) > 0 Then aconcat = Left(aconcat, Len(aconcat) - Len(sep)) Else aconcat = ""
End Function

I think that these are correct but haven't tested them. Both will need to be array confirmed with Ctrl Shift Enter.

=ACONCAT(IFERROR(SMALL(IF(E5:AA5=0,$E$1:$AA$1),{1;2;3}),""),", ")
and
=ACONCAT(IFERROR(INDEX($E$2:$AA$2,N(IF(1,SMALL(IF(E5:AA5=0,$E$1:$AA$1),{1,2,3})))),""),", ")