referencing spilled ranges as the table array in lookup... but not spilling

in row 4 from D4 onwards i have a transposed spilled range of stock tickers (UNIQUE from a table reference). Each has their stock price history on a sheet of its own, the matching sheet names are populated on row 3 with spill reference to D4 in D3.

As per example in D5 i can xlook up in col A and return from col B from each individual sheet using an INDIRECT reference to the sheet names in D3, and i can drag this to the right no issue. This is how i steer the lookup to individual sheets depending on the stock it's looking up.

However, seeing as the stocks are spilled and therefore can grow i want this lookup to not reference D3, D4, D5 ... via INDIRECT but to reference the spilled range via D3# instead however this is not working... see below - i keep getting N/A after turning "=XLOOKUP($B9;INDIRECT(D3&"A:A");INDIRECT(D3&"B:B");;FALSE)" into "XLOOKUP($B9;INDIRECT(D3#&"A:A");INDIRECT(D3#&"B:B");;FALSE)"

Funny thing is that with F9 the formula DOES evaluate and finds the price:

After searching i tried wrapping it in SUM, use N or T with INDIRECT etc... to no avail... I am out of ideas now and looking for some help here!

The INDIRECT function cannot be lifted over an array on its own. Try using the MAP helper function to iterate through the array and process each item individually:

=MAP(D3#;LAMBDA(wsName;XLOOKUP($B9;INDIRECT(wsName&"A:A");INDIRECT(wsName&"B:B"))))

Or by using the D4# spill range directly:

=MAP("Stock_"&D4#&"!";LAMBDA(wsName;XLOOKUP($B9;INDIRECT(wsName&"A:A");INDIRECT(wsName&"B:B"))))