Say you have values 1-7, uniquely in A1:A7, and some values of some kind in B1:B7, and do an INDEX/MATCH formula with MATCH using data in D1:D3 as lookup values in B1:B7. Spill functionality will yield three results for that, let's say all successful so value sets like 7,1,5 are produced.
That formula is not ITSELF exactly a dynamic range. It yields the spill result, but that's just half of it.
Say the formula is in E1. It spills to fill E1:E3, or as it can be addressed, E1#. So far, so good and it seems that is all. Reference E1# and you get all the pleasures of a DR. But use the formula in it instead and you do not. The DR is the result, not the formula.
You can reference the result, which IS a DR, and it acts just as you expect. But placing the formula in there means you are no longer referencing a DR, just the formula that leads to one. And sadly, that's not quite enough to make it work.
Accordingly, the INDEX/MATCH then acts like a single result formula, taking the "upper left corner cell" of its internal results array, and passing forward that single data point. In the above example, that's, say, a 7. And while AGGREGATE is happy to take a single input if that input is a cell reference, it will not accept an actual value instead. So it becomes =AGGREGATE(12,0, 7 ) and chokes on that 7. It would be happy if the 7 were "A7" and A7 held a bare 7, but it won't directly take a 7 itself. (Not for "12" anyway. I did not experiment to see if other of its functions would.)
One way to get around that would be to place the INDEX/MATCH formula into a Named Range and refer to the Named Range which would then take the place of an "E1#" style reference.
However, the seemingly related way of using LET and placing it in a named variable, then using that named variable in LET's working formula does not work. That is because while LET seemingly creates Named Ranges with a scope of the cell itself, it doesn't really. (Sadly, MS did not make that leap when they created LET and there are important differences.)
Not all is lost for LET though... it actually is, in its way, exactly analogous to the non-LET situation. If you use a "double variable" on the following pattern:
=LET(cow, INDEX(A1:B7,XMATCH(D1:D3,B1:B7,0),1), pig, cow, pig)
in which you define "cow" via the spilling formula, then define another variable, "pig" in this case, as equaling "cow", then you've done the analogue of having the formula in a cell and it spilling, then referring to that cell rather than the formula itself. You've used the two steps to end up with a true DR to refer to and now you can and still get the full result. If you changed the above's working formula from the bare "pig" to an AGGREGATE function using "pig" you'd have success.
So you can actually use LET, with that extra effort/step, and all your referents are in the single cell making understanding it two years from now much easier and removing the chance someone might delete or modify your Named Range.
(Sigh...) except you still can't. Excel just doesn't allow it either in the working formula or in placing the AGGREGATE around the LET. It's very sad.
But although I haven't the time now to pursue it, when Excel generates an internal array (like that INDEX/MATCH does), it keeps it although it only seems to pass forward the first element. If one can find a function to wrap it with that will handle an array, that internal array can "jump over" the function that created it and is trying to chop it down to a single element. The full array is then passed into the rest of the formula and you can get full results from it. This broad subject is called coercion in Excel and can be used to achieve other seemingly forbidden results as well. So if someone can find a function that will resuscitate that internal array, so to speak, and not do anything overly unhelpful at the same time, the array can be acted upon by something wrapping that coercer. But it can be hard to find something like that and it's 3:19 am here so I pass the baton.
In the meantime, you can definitely and confidently use the Named Range as the second step.
