Forum Discussion
Filter with IFERROR
Cannot attach an excel sheet.
Filter returns
=Filter(A:B,A:A="Office")
| A | B |
| Office | less than 255 character |
| Office | less than 255 character |
| Office | less than 480 character |
| Office | less than 255 character |
Now if I club this formula with IFERROR, it only returns
Formula - Iferror(Filter(A:B,A:A="Office"),"NA")
| A | B |
| Office | less than 255 character |
| Office | less than 255 character |
| Office | NA |
| Office | less than 255 character |
The formula is working fine, I have attached the sample file for your reference.
=IFERROR(FILTER($B$10:$C$13,$B$10:$B$13="Office"),"NA")
If you are unable to resolve the issue request you to please re-create the file removing the confidential information and upload the same
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer and like to help the other members find it more
I have a look at the screenshot. My problem is that when a cell has more than 255 characters then excel returns error.
I have used LEN function to calculate total characters
In addition. I guess from practical point of view IFERROR() is needed to replace #CALC error if filter is not match by some text. If so, the workaround could be
=IF(COUNTIFS(A:A,"Office"), FILTER(B:B,A:A="Office"), "NA")Interesting question. That's not only with IFERROR, with any function which works with arrays. For example
My guess array element can't be more than 256 in size. In you sample, FILTER itself returns the range into the sheet. If we wrap it with another function it returns an array to that function and #VALUE! appears for too big element of the array.
Same in my sample, C#:C6 first return an array to IF() and within it we have error for large (in size) element. Simple =C3:C6 works.
Not sure about workaround if it exists.
You are right, VishalRathore .
Actually, Filter - Iferror Functions Combination doesn't return cell text value if the number of characteristics is more than 256.Should find solution:)
