TEXTJOIN IF- not listing all items (products)

[impressive]

Hello everyone,
this is my first thread here in this forum. For quite some I am using excel now with great fun and passion. Thanks to this forum I was able to solve many obstacles when I got stucked.

However, today I really need to ask for help as I am stucked for a few days already.

So here is my problem:

My goal is to list all products with a particular Item number (for example 1001) in a cell- separated with a comma.
In my excel sheet are many products listed.
So for example:
1001 Zylinder
1001 Gasket
2001 Maintenance Cost
1001 project costs
2001 Hotel and allowance

I tried the following formula:
=TEXTJOIN(",";TRUE;(IF('sheet1'!$A$14:$A$600=1001;'sheet'!$E$14:$E$600;"")))

This also works almost perfectly fine- however, in the cell where excel should list the products, only the first item is listed.
Example of what I want: Zylinder, Gasket, project costs
Example of what I get: Zylinder

It seems to me that excel is listing the first item and stops after, but the question is why?

I tried it also with the very basic example where I put all words and formula on one sheet and it works perfectly fine. for example:

1​	Hello	I , am, now, at, home, HELLO	Here is the formula that i wrote into C1: =TEXTJOIN(", ";TRUE;(IF($A$1:$A$10=5;$B$1:$B$10;"")))
5​	I
5​	am
5​	now
5​	at
5​	home
3​	right
3​	now
5​	HELLO

Can anyone help me please?

 

[impressive]

I just found a typo in my previous post:
the formula is:
=TEXTJOIN(",";TRUE;(IF('sheet1'!$A$14:$A$600=1001;'sheet1'!$E$14:$E$600;"")))

 

[Fluff]

Did you confirm the formula with Ctrl Shift Enter, or just enter?

 

[impressive]

Yes I did

 

[Fluff]

In that case try

=TEXTJOIN(",";TRUE;IF(VALUE('sheet1'!$A$14:$A$600)=1001;'sheet1'!$E$14:$E$600;""))

 

[impressive]

I have tried and I get as a result: #VALUE!

 

[Fluff]

That suggests that you have text values in col A, or maybe a formula that returns ""

 

[impressive]

Well... the Values in Column A and C are from another sheet

 

[Fluff]

If your formula is only picking up some values, then it is almost certainly because you have a mix of numbers & text. You will need to make sure they are all one or the other, but not a mix.

 

[impressive]

Ok so tried to format the sheets as general and then as text but this one didnt help.
What I tried then was to get the text from the sheets where the items and the number are coming originally from (Lets call them sheet x, sheet y and sheet z ) and this worked.

So question now is: how can I make sure that the column A and C in Sheet1 are taking the values of sheets x,y and z the right way?