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

The following snippet:

 

= LAMBDA(assemblyName;
 LET(
   filteredParams; {"a";"b";"c"};
   mappedValues; {1;2;3};
   transposed; MTRANS(HSTAPELN(filteredParams; mappedValues));
   return; WENN(filteredParams = ""; "NV"; transposed);
   return
))("fake")

Gives me the following output:

a	b	c
1	2	3
#NV	#NV	#NV

At the same time, the following slightly different code (letting alone the output of WENN):

= LAMBDA(assemblyName;
 LET(
   filteredParams; {"a";"b";"c"};
   mappedValues; {1;2;3};
   transposed; MTRANS(HSTAPELN(filteredParams; mappedValues));
   return; WENN(filteredParams = ""; "NV"; transposed);
   transposed
))("fake")

Spits out:

a	b	c
1	2	3

This is not the only case, but is the simpliest, I can reconstruct this kind of error with.

Do not mind the logic, it is an edited excerpt, just for illustration.

P. S. It is German syntax. English would have TRANSPOSE, HSTACK, IF,... instead.  Maybe a little difference in punctuation.

 

excel
Formulas and Functions
office 365

2 Replies

  • 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.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    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 🙂

Resources