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

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

VSTACK.xlsx13 KB

KherSheon

Copper Contributor

Jan 19, 2025

This one works well for me. I replaced errors with blanks.

Just a probelm, Excel cannot return a true Blank. So I created a Name "BlankCharacter" and refer it to a truly blank cell.

SergeiBaklan

Diamond Contributor

Jan 21, 2025

KherSheon , sorry, I didn't catch what you try to do. Excel doesn't work with blank as a value. Finally returning result to the grid you have zero or empty string.

Forum Discussion

Formula VSTACK/HSTACK with blank array

Resources