Forum Discussion

kayem's avatar
kayem
Copper Contributor
Feb 04, 2026

Formula help

Hi all,  I have a spreadsheet with four sheets of data (different suppliers, then organised by catalogue/non-catalogue products), and I want the product name to pull through to a fifth summary sheet...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 05, 2026
=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.

  

  • kayem's avatar
    kayem
    Copper Contributor
    Feb 05, 2026

    Thanks so much! How do I use the vstack, choosecols, filter to return results from the four sheets in one step? 

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Feb 07, 2026
      =LET(colsfromsheets,
CHOOSECOLS(VSTACK(Tabelle1:Tabelle3!A3:J200),2,5,7,10),
IFNA(VSTACK({"Non-Catalogue".""."Catalogue"."",
"Product"."Number Required"."Product"."Number Required"},
HSTACK(
FILTER(CHOOSECOLS(colsfromsheets,1,2),CHOOSECOLS(colsfromsheets,2)>0),
FILTER(CHOOSECOLS(colsfromsheets,3,4),CHOOSECOLS(colsfromsheets,4)>0))),""))

      Lorenzo​ has provided a solution for Microsoft 365 in the meantime. I'd have tried to achive the result with the formula above.

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Feb 07, 2026

        Nice one OliverScheurich​ 

        Looks like kayem​ doesn't see the benefits of Structured Tables yet - mentioning the main in case this could make a difference in the future...

        • Dynamic ranges (instead of provisioning i.e. 1K rows when 100 are actually used) = efficiency
        • Using structured references make formula more comprehensive
        • Formulas, cells formatting, conditional formats, data validation… auto. apply to new rows added to the Table
        • If the Table Total Row is activated and contains formulas using structured ref., new rows are picked up auto
        • ...