Excel TEXTJOIN not reading values of another sheet

[Gonzs12]

Hi,

I have used the TEXTJOIN formula in plenty of files before and it worked just fine. I did the same thing on a new file and it doesn't work. Same terminology, same query, same format, etc. but I get different errors depending on which "text" I choose for the formula.

In this case I need to look for the value of G29 (located in sheet1) in column B of the 'Auto NC' tab (sheet 2). If the value matches I expect to get in return the values of column H of the same 'Auto NC' tab separated by commas.

I've tried these 3 formulas and they all give me an error.
=TEXTJOIN(",",TRUE,UNIQUE(G29='Auto NC'!B:B , 'Auto NC'!H:H,""))

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

For example, assuming G29=1058 . This value shows up 6 times in column B of 'Auto NC' tab. I should be getting the 6 results from column H separated by commas, but I only get a #value error or a #spill error or an empty cell or a #calc error (textjoin: text too long).

Column H shows the following for 1058:

NC 1104 (DispositionDraft) Rework: Draft
NC 1102 (Submitted) :
NC 1098 (DispositionDraft) Evaluation: Draft
NC 1089 (DispositionDraft) Repair: Draft
NC 1078 (WorkInProgress) Repair: InProgress
NC 1075 (DispositionDraft) Repair: Draft

I verified that the Auto NC tab doesn't have any empty cells or error cells. And the 6 results add up to 246 characters which is way less than the maximum Excel can handle. Also, all the cells are in the same format (general), just like in the rest of my files. 

Extra info:
1) I evaluated the formulas and it seems like the TEXTJOIN is not reading the values on the column B of 'Auto NC'.
The evaluation I get for the "IF" text while using only 20 rows is:

=TEXTJOIN(",",TRUE,IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 'Auto NC'!$H$2:$H$20, ""))

which ends up giving me an empty cell.

2) I tried already simplifying the range from both columns to 2-20 rows and I still get an error.
=TEXTJOIN(" ,",TRUE,UNIQUE(G29='Auto NC'!$B$2:$B$20,'Auto NC'!$H$2:$H$20, ""))

3) I re-created the Excel file from scratch, and still no luck.

4) The values from columns B and H are vlookup formulas. They get their info from a query in sheet3. Once again, I've been doing this for months on all my Excel files and this is the only one not working. Not sure what else to do at this point.

Any help is appreciated. Thank you!

Thank you!

[#2]

@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.