Forum Discussion

Matt_Paz's avatar
Matt_Paz
Copper Contributor
Feb 18, 2025

Dealing with VSTACK with empty arrays

Hi all

Question - do you know how to still display data when there is an empty array within VSTACK? Currently my formula works but displays CALC when an array is empty.

I have two tables, both with data filtered based on a particular month.

The spreadsheet example works but needs maybe LET & ISERROR to ensure one empty array of results still displays the remainder.

Can anyone advise please?

 

=IFERROR(SORT(VSTACK(FILTER(FILTER(A3:E6,(D3:D6<=C9)*(E3:E6>=C9)),{1,1,0,1,0}),FILTER(FILTER(H3:L6,(K3:K6<=C9)*(L3:L6>=C9)),{1,1,0,1,0})),3,1,FALSE),"ERROR")

Thank you

 

excel
Formulas and Functions

3 Replies

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 18, 2025
    =LET(stacked,VSTACK(A3:E6,H3:L6),IFERROR(SORT(FILTER(FILTER(stacked,(INDEX(stacked,,4)<=C9)*(INDEX(stacked,,5)>=C9)),{1,1,0,1,0}),3,1,FALSE),"ERROR"))

    This formula works if there is no match in range H3:L6. You can as well clear all data in range H3:L6 and the formula still returns the intended result. The same applies for range A3:E6.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 18, 2025

    As variant

    =LET(
    v, VSTACK(
       FILTER(A3:E6, (D3:D6<=C9)*(E3:E6>=C9), FALSE ),
       FILTER(H3:L6, (K3:K6<=C9)*(L3:L6>=C9), FALSE ) ),
    IFERROR( CHOOSECOLS( FILTER(v, CHOOSECOLS(v,1) ), 1,2,4), "no data" )
 )
    • Matt_Paz's avatar
      Matt_Paz
      Copper Contributor
      Feb 18, 2025

      Thank you

       If I was to have a different column order in one of the source tables, can choosing columns be independent to each array?
       If so can the formula be adapted?

       

       

       

Resources