Converting TEXTJOIN to CONCATENATE

Does anyone know how to translate =TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT(Sheets[Sheets]&"!1:1048576"),$D5)>0,Sheets[Sheets],"")) into a CONCATENATE function. I do not have a 365 subscription so TEXTJOIN isn't available. Thanks for the help.
Attached are pictures because the file was too large.

EnableEditing.PNG
Protected View.PNG

You will need VBA in a user defined function (UDF) for this.

Here is one by tigeravatar.

Please Login or Register  to view this content.

If you are not familiar with how to install VBA code:

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the code into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

The syntax of the new formula:

Concatall(the_array,[optional: separator],[optional: Unique_only True/False])

This may need to be array entered.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

If performance is slow consider reducing the range in the INDIRECT to just a few more rows than you anticipate needing. While COUNTIF will take whole columns the results are being passed to this UDF. It may be doing a lot of unnecessary work.