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

=IFERROR(INDEX(Tabelle1!$B$3:$B$1000,
SMALL(IF(ISNUMBER(Tabelle1!$E$3:$E$1000),
ROW(Tabelle1!$E$3:$E$1000)-2),ROW(A1))),"")

This formula works in my sample file in modern and legacy Excel for the non-catalogue products of sheet "Tabelle1". The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.

It returns the results from one sheet to the summary sheet. You can adapt these formula to all four sheets. The other formulas are in the attached file.

If you have access to modern Excel and it's functions such as VSTACK, CHOOSECOLS, FILTER... then there's a possibility to return all results from the four sheets in one step.