Forum Discussion
When FILTER is used in an IF statement only 255 characters can be returned else #Value! error
I'm having an issue trying to return the results of a filter without "0"s, because the value 0 actually means something in the data I am filtering.
To get around this I tried the following
=IF(ISBLANK(FILTER(DataSheet!$A$2:$V$5000,DataSheet1!$A$2:$A$5000=ThisSheet!$C$3)),"",FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3))
But for any of the cells that have over 255 characters in DataSheet the above returns #Value! error
Any help for resolving the error or for displaying Filter results with 0 the character being kept but a null cell being represented by null or an empty string ""
I see, that's interesting. I guess that's the limitation of any if() function - IF(), COUNTIF(), etc. They don't work with constants more than 255 characters, and in your case before array converted to range this limitation works.
Variant, if you work with texts, could be
=LET(str,FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3), LEFT(str,1500) )It there are numbers within the range they will be returned as texts.
9 Replies
No one formula in Excel could return blank value, thus ISBALNK(FILTER()) doesn't work, it's always FALSE. I guess the question is if C3 is blank. You may try
FILTER(DataSheet!$A$2:$V$5000, (DataSheet!$A$2:$A$5000=ThisSheet!$C$3) * (DataSheet!$A$2:$A$5000 <> "") )to exclude blanks from filtering. However, not sure which logic you'd kike to apply.
- Thanks but the code you've presented just returns the whole table.
It's a bit of a hack but you can execute the formula FILTER(DataSheet!... , DataSheet! = C3) and after the values are on the spreadsheet you can put ISBLANK() around the formula which will then display the true and false values, which is how you can get my original code to either display the text or the "", but in cells with over 255 characters it returns #Value!I see, that's interesting. I guess that's the limitation of any if() function - IF(), COUNTIF(), etc. They don't work with constants more than 255 characters, and in your case before array converted to range this limitation works.
Variant, if you work with texts, could be
=LET(str,FILTER(DataSheet!$A$2:$V$5000,DataSheet!$A$2:$A$5000=ThisSheet!$C$3), LEFT(str,1500) )It there are numbers within the range they will be returned as texts.
