Forum Discussion

qazzzlyt's avatar
qazzzlyt
Copper Contributor
Nov 30, 2024
Solved

Formula VSTACK/HSTACK with blank array

I have a formula =VSTACK(a,b,c,d), while length of each array is not fixed. Now, sometimes, let's say d is blank, or N/A. I want the result to be the stack of a+b+c. One solution is to use a IFERRO...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 02, 2024

    As variant

    =LET(
     FilterRange, LAMBDA(rng,id,name,
       IFERROR( FILTER(rng, CHOOSECOLS(rng, id) = name ), "⚽" ) ),
     Combined,  VSTACK(
       FilterRange( A1:B1000, 1, "Bird" ),
       FilterRange( D1:E1000, 1, "Dog"  )
     ),
     FILTER(
       Combined,
       NOT( ISNA( CHOOSECOLS(Combined, 2 ) ) ) )
)
Kidd_Ip's avatar
Kidd_Ip
MVP
Nov 30, 2024

Try below:

 

=LET(
    arrs, {a, b, c, d},
    validArrs, FILTER(arrs, NOT(ISNA(arrs))),
    VSTACK(validArrs)
)

 

Resources