IFERROR reads a long text as Error when Input is a Range

amit_bhola 

It works on Excel which doesn't support dynamic arrays, and doesn't work on Excel with dynamic arrays.

It looks like IFERROR() returns an error if element of the array is more than 255 characters length. If you work with one element (or, the same, without dynamic arrays) it takes only one element. If you convert the range into array another logic works, function consider elements with <= 255 characters only.

You may test

=IFERROR(LEFT(B3:B4,255),"error") - works

=IFERROR(LEFT(B3:B4,256),"error") - doesn't work

Thanks all, pls. allow me to reply you all in a single post,

SergeiBaklan  Thanks for the suggestion, it seems a better way of trapping the IFERROR error. But strangely though, the other functions taking long text as input/output are working fine, e.g. i checked =UPPER(B3:B4) and it worked perfectly OK. The 255 array element length limit behaviour seems to be specific to IFERROR only.

Lorenzo  Thanks, i confirm the D3 #VALUE! test. It shows the limit to be of 255 for IFERROR reading an array/range. But as i write above, the limit is not there for other functions like UPPER.

As for whether it is a bug or not, a formula behaving differently as per excel version, or not giving its output as an error or ##### string in case limit is reached and instead gives an unexpected non-intuitive output is certainly something which needs some attention, at least documentation. But that's for MS to do.

By Preview file, i meant clicking the file name in the original post instead of downloading the file. You may confirm that it works fine in the preview mode, by clicking the yellow highlighted in the original post.

HansVogelaar Detlef_Lewin  Thanks, but i cannot drag fill or cannot avoid using IFERROR because my actual application of this function involves outputting an array. In this post i simplified the question leaving out that detail.

amit_bhola 

The limit is 255 characters. 255 is the largest number that fits into 1 byte (8 bits); it is a limit that occurs in many places in Excel.

You already know how to avoid it: use =IFERROR(B3,"Error!!") and fill down.

amit_bhola 

(I didn't understand what you exactly mean with Document preview)

IMHO it's MSFT's call to say bug/not bug. Here is a possible explaination though:

The max. length of a text value in a formula is 255 char. Enter the following in a cell (len = 255):

="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

=> OK

Try adding 1 char. to the above formula:

Do the same with an array. In another cell enter (len = 255):

={"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"}

=> OK

Try adding 1 char to the above array:

In the file you shared: Select D3 > Edit the formula in the formula bar > Select B3:B4 only > F9:

amit_bhola 

Why use IFERROR() in the first place? There is no error in a text string.

Just =B3:B4 works fine.

amit_bhola 
Love SergeiBaklan's response - simple fix!
I did find a way to keep the entire text though: instead of IFERROR, use ISERROR:
Change this:

IFERROR(B3:B4,"Error!!")

To this:

IF(NOT(ISERROR(B3:B4)),B3:B4,"Error!!")

TIP: If your range is more complicated than that, to avoid entering it twice, you can encapsulate it like this: 

LET(MyRange,B3:B4,IF(NOT(ISERROR(MyRange)),MyRange,"Error!!")

NOTE: MATCH() also can't handle >255 chars. That's where Sergei's solution comes into play:

MATCH(LEFT(MyLookupValue,255),LEFT(MyRange,255),0)