Stack n named ranges by using a loop without VBA

Try using REDUCE() function here:

=DROP(REDUCE("", XLOOKUP(SEQUENCE(2), 
                         Info[ID], 
                         "range"&Info[Name], ""), 
      LAMBDA(x,y, VSTACK(x, INDIRECT(y)))), 1)

The above formula stacks a bunch of named ranges into one list. It builds the range names from a lookup table, then pulls them together. To explain step-by-step:.

• SEQUENCE(2) --> This generates {1; 2} which is used to grab the first two IDs from the Info table

• XLOOKUP(SEQUENCE(2), Info[ID], "range"&Info[Name], "") --> This looks up IDs 1 and 2 in the Info[ID] which returns "range" concatenated with each match from Info[Name]

• The output will be {"rangeA"; "rangeB"}

• The empty quotes just to handle scenarios where nothing matches.

• REDUCE("", ..., LAMBDA(x, y, VSTACK(x, INDIRECT(y)))) --> This starts with an empty value and then it iterates through each name from XLOOKUP() function.

• INDIRECT() function for each iteration turns the text "rangeA" into an actual range.

• VSTACK() function keeps appending each range under the previous one and by the end, everything is stacked in one column.

• DROP(..., 1) --> Since REDUCE() started with an empty value, we get a blank row at the top and this removes that first row so only the real data stays.

To make it more readable could try:

=LET(
     a, Info,
     b, SEQUENCE(ROWS(a)),
     c, XLOOKUP(b, Info[ID], "range"&Info[Name], ""),
     REDUCE({"ID","Value"}, c, LAMBDA(x,y, 
     VSTACK(x, INDIRECT(y)))))

Where Info is the name of the Table with IDs and its respective Names.

Alternatively, using Power Query:

To use Power Query follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Info

----------

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

----------

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

----------

let
    Source = Excel.CurrentWorkbook(){[Name="Info"]}[Content],
    DataType = Table.TransformColumnTypes(Source, {{"ID", Int64.Type}, {"Name", type text}}),
    JoinRangeName = Table.AddColumn(DataType, "RangeName", each "range" & [Name], type text),
    AddTableContents = Table.AddColumn(JoinRangeName, "TableContent", each
        let
            RangeName = [RangeName],
            MatchingTable = Excel.CurrentWorkbook(){[Name = RangeName]}[Content]
        in
            MatchingTable),
    RemovedOtherCols = Table.SelectColumns(AddTableContents,{"TableContent"}),
    Answer = Table.ExpandTableColumn(RemovedOtherCols, "TableContent", {"ID", "Value"}, {"ID", "Value"})
in
    Answer

- Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall creates a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

----------