Forum Discussion

Copper Contributor

Jan 21, 2026

Solved

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

NikolinoDE

Jan 23, 2026

=LAMBDA(assemblyName;
 LET(
   filteredParams; {"a";"b";"c"};
   mappedValues; {1;2;3};
   stacked; HSTAPELN(filteredParams; mappedValues);  // 3x2 array
   transposed; MTRANS(stacked);                     // 2x3 array
   return; WENN(transposed = ""; "NV"; transposed); // Dimensions match (2x3)
   return
))("fake")

for eliminating the #NV padding.

Hope it helps 🙂

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.