TEXTJOIN IF formula returning #VALUE!

Question

Hi,

I am trying to get the following formula working in a cell on my (fairly large) file: {=TEXTJOIN(", ",TRUE,IF($K$2:$K$10=D2,$CS$2:$CS$10,""))}

However, it is currently returning #VALUE! and I cant for the life of me figure out why. Please see below screenshot of the cells being referenced (I have hidden the in between cells):

I have tried in cell CK6 to just do the TEXTJOIN without the IF and this seems to have worked, so the error seems to be connected to the IF formula within the TEXTJOIN.

The function arguments shows that the if is finding the right cells but returning them as #VALUE! rather than the actual value.

The simple TEXTJOIN formula in Cell CK6 seems also show #VALUE! in the function arguments, but does in fact join the strings together:

I have a very similar formula working in other cells in this same file, see below:

This produces:

Thanks in advance for you help.

Answer 1

You seem to have very long strings in CS: If any one cell exceeds 255 characters, TEXTJOIN returns the #VALUE! error.

So try shortening the strings in CS, if possible. And if you are handling larger ranges, if all the cells are shorter than 256 characters but the resulting string is longer than 32767 characters, you will also get that error.

Answer 2

Hey Bernie,

Yes it does seem like that is the problem, I had found the 32767 characters limit when I was researching online but not the 255 character limit per cell, what I had in CS was 264 characters per cell.

Thanks for your help!

Amber