Aug 30 2022 01:57 AM - edited Aug 30 2022 02:00 AM
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!
Aug 30 2022 03:04 AM
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.
Aug 30 2022 03:18 AM - edited Aug 30 2022 03:19 AM
@Harun24HR okay, I will check and get back to you. Thank you so much
Aug 30 2022 03:32 AM - edited Aug 30 2022 03:42 AM
@Harun24HR Good lord, you are a life saver, thank you thank you so much. Wish you all the success in life
Aug 30 2022 03:04 AM
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.