Filter with IFERROR

%3CLINGO-SUB%20id%3D%22lingo-sub-1733748%22%20slang%3D%22en-US%22%3EFilter%20with%20IFERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1733748%22%20slang%3D%22en-US%22%3E%3CP%3EI%20used%20the%20FILTER%20formula%20to%20filter%20few%20row%20that%20contain%20txt.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20when%20I%20use%20IFERROR%20to%20avoid%20getting%20error%2C%20cells%20that%20have%20more%20than%20256.%20How%20can%20I%20avoid%20this%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1733748%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1733787%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20with%20IFERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1733787%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816118%22%20target%3D%22_blank%22%3E%40VishalRathore%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20attached%20the%20sample%20data%20%26amp%3B%20share%20the%20screenshot%20of%20the%20error%20you%20are%20getting%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20and%20like%20to%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1733830%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20with%20IFERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1733830%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECannot%20attach%20an%20excel%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3EFilter%20returns%3C%2FP%3E%3CP%3E%3DFilter(A%3AB%2CA%3AA%3D%22Office%22)%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E%3CSPAN%3EB%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20255%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20255%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20480%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20255%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20if%20I%20club%20this%20formula%20with%20IFERROR%2C%20it%20only%20returns%3C%2FP%3E%3CP%3EFormula%20-%20Iferror(Filter(A%3AB%2CA%3AA%3D%22Office%22)%2C%22NA%22)%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E%3CSPAN%3EB%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20255%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20255%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CFONT%20color%3D%22%23ff6600%22%3E%3CSPAN%3ENA%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3EOffice%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CSPAN%3Eless%20than%20255%20character%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I used the FILTER formula to filter few row that contain txt. 

 

However, when I use IFERROR to avoid getting error, cells that have more than 256. How can I avoid this error?

8 Replies
Highlighted

Hi @VishalRathore 

 

Could you please attached the sample data & share the screenshot of the error you are getting 

 

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

Highlighted

Hi@Faraz Shaikh 

Cannot attach an excel sheet. 

Filter returns

=Filter(A:B,A:A="Office")

AB
Officeless than 255 character
Officeless than 255 character
Officeless than 480 character
Officeless than 255 character

 

Now if I club this formula with IFERROR, it only returns

Formula - Iferror(Filter(A:B,A:A="Office"),"NA")

AB
Officeless than 255 character
Officeless than 255 character
OfficeNA
Officeless than 255 character

 

Highlighted

Hi @VishalRathore 

 

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")

 

Snag_316c44b6.png

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

Highlighted

Hi@Faraz Shaikh 

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

 

Highlighted

You are right, @VishalRathore .
Actually, Filter - Iferror Functions Combination doesn't return cell text value if the number of characteristics is more than 256.

 

Edgar_Martin_0-1601552396100.png

Should find solution:)

 

Highlighted

@VishalRathore 

 

Please follow the below link

http://excel.tips.net/T003163_Charac...for_Cells.html


Just mention that you can enter up to 32,767 characters in a cell of later versions of Excel and display 1 024 characters in a cell.

Highlighted

@VishalRathore 

Interesting question. That's not only with IFERROR, with any function which works with arrays. For example

image.png

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.

Highlighted

@VishalRathore 

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")