Formula help

Hi kayem​ 

You seem to be new here so one recommendation at least:
Always mention the version of Excel in use & on which platform (Windows/Mac/Web…)

Below & attached is a 365 option where data in Sheets 1-3 have been formatted as Structured Tables - highly recommended in your case, respectively named NonCatalogue1, NonCatalogue2... & Catalogue1, Catalogue2...

NonCatalogueNames, CatalogueNames & ColumnNames are Defined Names. Under the first 2, list the name of the Tables to combine. According to your description only NonCatalogue4 & Catalogue4 should be missing

The LAMBDA named CombineTables combine/append the data from the Table names listed in i.e. NonCatalogueNames, filtering ( [Required] > 0 )

NB: If you don't run an English version of Excel a little adjustment must be made to the LAMBDA - line 6 - where [#All] must be replaced with [#Xyz] where Xyz is language specific (if you don't run an EN version & don't know how Xyz translates in your language, provide the latter)

LAMBDA CombineTables:

=LAMBDA(TableNames,
  LET(
    COMBINE,
      LAMBDA(init, tableName,
        LET(
          table,  INDIRECT( tableName  & "[#All]" ),
          data,   IF( ISERR( TAKE( table, 1, 1 ) ), "",
                    LET(
                      headers,  TAKE( table, 1 ),
                      data,     CHOOSECOLS( DROP( table, 1 ), XMATCH( ColumnNames, headers ) ),
                      FILTER( data, CHOOSECOLS( data, 2 ) > 0, "" )
                    )
                  ),
          VSTACK( init, data )
        )
      ),

    ToCombine,      FILTER( TableNames, ISTEXT( TableNames ) ),
    Combined,       DROP( REDUCE("", ToCombine, COMBINE ), 1 ),
    FilteredEmpty,  FILTER( Combined, ISNUMBER( CHOOSECOLS( Combined, 2 ) ) ),
    
    VSTACK( TOROW( ColumnNames ), FilteredEmpty )
  )
)