Forum Discussion

PPM's avatar
PPM
Copper Contributor
Jan 21, 2026

Excel pads dynamic array output with #NV values

I have come across a couple of instances, where Excel 365 pads the output (of lambdas) or in this case of a specific formula within a lambda with #NV values (or errors if you like to treat them so.) ...
excel
Formulas and Functions
office 365
PPM's avatar
PPM
Copper Contributor
Jan 23, 2026

Nikolino, thank you!

This was dynamic array behaviour of the formulas, I see.

= LET(
   filteredParams; {"a";"b";"c"};
   transposed; MTRANS(filteredParams);
   criterium; filteredParams = "";
   return; WENN(criterium; "NV"; transposed);
   return
)

It produces an array of Booleans for the criterium:

FALSE
FALSE
FALSE

And then for each of them, a row of transposed (a kind of Cartesian product.):

a	b	c
a	b	c
a	b	c

So, in the case, I first have to assess whether filteredParams is a scalar (one column and one cell,) and then compare it to "" to get rid of multiplication.

Resources