Multiple Filter() functions in one column

[ThatOneDude]

I have a spreadsheet that is performing lookups via the Filter() function through several large databases and importing lots of data from them into the main sheet.

Some of the Filter lookups have multiple results and currently everything is working perfectly by using TEXTJOIN to put all the results into one cell as shown below. There is a formula elsewhere that enters the numbers that are needed and then the names show up automatically. Since they are in consecutive rows, this works perfectly.

Book2
	D	E
4	Number	Name
5	1	JIM AMY JUSTIN MICHAEL ETHAN FRANK
6	2	STEVE JAMES DENNIS
7	3	LINDSEY KRISTINA
8	4	MATT ANGELA JASON JOHN PETER BRIAN CHRIS SCOTT
9	5	RYAN
Sheet1

Cell Formulas
Range	Formula
E5:E9	E5	=IF($D5="", "", IFERROR(LET(x,FILTER($U$10:$U$29, (ISNUMBER(SEARCH($D5, $T$10:$T$29)))),TEXTJOIN(CHAR(10),FALSE, IF(x="","*Data Missing*",x))), "Not Listed"))

What I'd really like to do, however, is to not TEXTJOIN them and instead have the formula put the number 1 first, spill down the FILTER results as far as they need to go and then automatically put the number 2 in the next empty row, spill those results down, then enter the number 3 and so on. Sometimes there is only one result. Sometimes there are twenty. I figured VBA was the best, or only, way to do this but I'm not sure how.

Book2
	D	E
14	1	JIM
15		AMY
16		JUSTIN
17		MICHAEL
18		ETHAN
19		FRANK
20	2	STEVE
21		JAMES
22		DENNIS
23	3	LINDSEY
24		KRISTINA
25	4	MATT
26		ANGELA
27		JASON
28		JOHN
29		PETER
30		BRIAN
31		CHRIS
32		SCOTT
33	5	RYAN
Sheet1

Cell Formulas
Range	Formula
E14:E33	E14	=IF($D14="", "", IFERROR(LET(x,FILTER($U$10:$U$29, (ISNUMBER(SEARCH($D14, $T$10:$T$29)))),IF(x="","*Data Missing*",x)), "Not Listed"))
Dynamic array formulas.

Thanks for the assistance.

 

[SullyWYO]

Here is a pretty easy method in Power Query. This will take the data in the table you showed above, and it will create a new table in your desired format (Replace "Table 7" with the name of your source table):

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Name", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Name", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}})
in
    #"Changed Type1"

 

[ThatOneDude]

I'm not sure if this will work for me. I provided a very simplistic example of my data set but what really happens is you input a value at the top of the sheet and then 80+ columns are filled with various lookups from 7 different databases located on subsequent sheets. One of those database datasets returns multiple results and those are the ones that need to spill down. Then I would like the next entry on the sheet to appear in the next empty row and then all 80+ columns would fill for that one and spill however far is necessary. I also need all of this to happen automatically after the user makes the entry at the top. This is exactly what's happening now except all the results are text joined in the same cell instead of spilling down.

 

[ThatOneDude]

Then if they simply change the entry at the top, all the data is removed and replaced with the new lookup results.