Why Excel Formula returns an array?

Question

I have a problem with the last SWITCH in this formula. If this condition ISERR(Articles) is met, it returns one single celle with the string, which is the expected behavior. But if the COUNT(...) condition is met, it returns an array of strings "Check ...". The number of rows is equivalent to the variable Length. And the number of cols is equivalent to the number of cols in Result. Why this and how to avoid it ?!? Thanks in advance.

=LET(
Articles;FILTER(Preview!B7:B1006; (Preview!D7:D1006<>0));
TranscodedArticles; FILTER('Transco Table'!A1:C9999; ISNUMBER(MATCH('Transco Table'!A1:A9999; Articles; 0)));
ExplodedArticles; INDEX(TranscodedArticles;0;2);
Length;COUNTA(ExplodedArticles);

EmptyColumn;IF(SEQUENCE(Length);"");
EmptyColumns;IF(SEQUENCE(Length;12);"");
DateColumn;IF(SEQUENCE(Length);TODAY());
DEVANColumn;IF(SEQUENCE(Length);DEVANCell);
ProjectColumn;IF(SEQUENCE(Length);ProjectCell);
Quantities; BYROW(TranscodedArticles; LAMBDA(row; IFERROR(SUMIF(Preview!B7:B1006; INDEX(row;1); Preview!D7:D1006)*INDEX(row;3);"Notfound") ));

Result; HSTACK(DateColumn; DEVANColumn; ProjectColumn; EmptyColumns; ExplodedArticles; EmptyColumn; Quantities);
SWITCH(TRUE;
   ISERR(Articles); "No lines with quantity found in Preview";
   COUNT(FILTER(Articles; ISNA(MATCH(Articles; INDEX(TranscodedArticles;0;1); 0)))) <>0; "Check and complete 'Tranco Table' ('Number Stored' as Text or unkown codes) ... ";
   Result
   )
)

COUNT returns an array if the input is an array. I think you mean: SUM(N(FILTER(Articles; ISNA(XMATCH(Articles; INDEX(TranscodedArticles;0;1)))) <>0)) — P.b
– P.b, Commented Sep 12 at 17:34
but n x 1 array is the default parameter arg for count - same as for Length returning single element value for COUNTA(ExplodedArticles) in OP Q... ? — JB-007
– JB-007, Commented Nov 20 at 8:27

JB-007 · Accepted Answer · 2025-11-20 12:51:56Z

Use iserr(index(Articles,1,1)) in switch expression to resolve:

i.e.

SWITCH(TRUE;
   ISERR(index(Articles,1,1)); "No lines with quantity found in Preview";
   COUNT(FILTER(Articles; ISNA(MATCH(Articles; INDEX(TranscodedArticles;0;1); 0)))) <>0; "Check and complete 'Tranco Table' ('Number Stored' as Text or unkown codes) ... ";
   Result
   )

Arguments you're comparing the switch expression (True) against should have same dimensions (the final argument i.e. Result doesn't need to though) ....

The count express ALWAYS returns a single element (by defintion)... (!)
BUT iserr(Articles) = True only when it's a single element (as underlying filter didn't evaluate):

In this case your switch is valid so it returns what you expect:

"No lines with quantity found in Preview"

When iserror(Articles) <> True, then it will be array {False, False,....} same length as Articles. so you end up with a concoction of the row count of Articles and the width (col count) of Results, all returning same text

"Check and complete 'Tranco Table' ('Number Stored' as Text or unkown codes) ... ";

Simple demonstration:

Variations of the original switch function per OP Q and the recommended solution (one of 'too many' ways this could be achieved btw..):
I'll provide full scope and function for 1st scenario - the others follow per the screenshot accordingly:

=SWITCH(TRUE,ISERROR(INDEX(F2#,1,0)),"A",COUNT(FILTER(F2#,ISNA(MATCH(F2#,INDEX(H2#,0,1),0))))>0,"B",J2#)

Here are the other scenarios showing how you get matrix results when Articles not error with original method; but you get expected result otherwise; finally - for some fun, Articles shown as 2 cols vs Results with 3 - with isrr(Articles) = {False, False, False... 2 col matrix this time)...

See B comes back with 3rd col now showing #N/A due to discrepancy of extra col in Results. However, if both A and B same dimension (in particular, 1 element), then this would not adversely impact the outcome (it would be correct).

¯\_(ツ)_/¯

Collectives™ on Stack Overflow

Why Excel Formula returns an array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related