Using FILTER function and VSTACK function together

PeteSauces 

OK.  Firstly, are you using 365 online or a desktop version of Excel (I use only the desktop version but I believe the on-line version lacks a Name Manager)?

I have assumed that the data held on your 10-15 worksheets are all located in the same place on each sheet.  That allows one to use a 3D range that spans multiple consecutive sheets, if not, a different strategy would be needed.  The syntax of a 3D range is

"productList"

Products:Clearance!$A$2:$A$21

That is the name of the first sheet, followed by a 'colon' symbol, then the name of the final sheet of the range, and closing with an 'exclamation mark'.  That string may be enclosed in single quotes if needed.

The sheet range is followed by a worksheet range that applies to every sheet.

From there TOCOL or VSTACK may be used to consolidate the data into a single array held in memory (here called 'consolidatedList').  I have used TOCOL because it allows the blank rows to be eliminated immediately, but the use of FILTER would have removed them in any case.

= LET(
    consolidatedList, TOCOL(productLists, 1),
    FILTER(
      consolidatedList,
      ISNUMBER(SEARCH(selectedCompany, consolidatedList))
    )
  )

I expect you will have more questions but first let's see whether this helps.