Excel TEXTJOIN not reading values of another sheet

Gonzs12 The syntax you've used for UNIQUE is incorrect, which is why it's returning #VALUE!, so you can eliminate this method as an option.

The IF method is not a good option here either, because it will return any blank cells in column H as zeros, which explains the #CALC! error (TEXTJOIN: text too long) when cell G29 is blank.

The best option you've presented is the FILTER method. If it's returning an empty string, that means no matches were found. The most likely explanation is that the value in cell G29 is numeric (1058) and the values in 'Auto NC'!B:B are text ("1058"), or vice-versa. Leading or trailing spaces in one or the other is also a possible cause. Try using the TEXT function and/or TRIM to see if it solves the problem. For example:

=TEXTJOIN(",", TRUE, FILTER('Auto NC'!H:H, TEXT(G29,"@")=TRIM('Auto NC'!B:B), ""))

Note: for performance reasons it's not a good idea to reference entire sheet columns in your formulas. Either use a reasonable number of rows that extends far enough beyond your dataset to allow for growth (ie: $H$1:$H$10000 and $B$1:$B$10000) or format the dataset as an Excel table and use structured table references.