Need help with #value! error

[nathtaani7]

Hello everyone,

I am getting #Value! error while trying to get an outcome with the below formula:

=TEXTJOIN("; ",TRUE,UNIQUE(IF(A1444=$A$3:$A$3060,$AG$3:$AG$3060,""))) 

Please note that the formula is working fine on majority of the cells i.e. 1806 but showing error in total 28 cells. I tried every basic things which removes this error but nothing worked. There is no space or numbering issue as it has taken number from other cells and it worked there. 

with the help of formula evaluation, I found that the error is particularly coming from this column $AG$3:$AG$3060, but I don't see anything to fix there.

Can anyone help me please? Truly appreciate your time and assistance!

Warm regards!

[#2 · Accepted solution]

@nathtaani7 Try FILTER() formula instead IF().

=TEXTJOIN("; ",TRUE,UNIQUE(FILTER($AG$3:$AG$3060,$A$3:$A$3060=A1444)))

• If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.

[#3]

@Harun24HR okay, I will check and get back to you. Thank you so much

[#4]

@Harun24HR Good lord, you are a life saver, thank you thank you so much. Wish you all the success in life