Forum Discussion
Trying to use a spill range as a reference for FILTER that uses INDEX
- Sep 14, 2024
=SORT(UNIQUE(DROP(REDUCE("",F3#,LAMBDA(u,v,VSTACK(u,FILTER(Table1[Power (kW)],Table1[Combined Values]=v,"")))),1)))
Does the formula in cell G3 return the expected result?
The main thing that makes your requirement difficult to meet is that your selection is an array but for each model selected you have an array of available power options. Microsoft messed up big time in specifying the functionality of array formulas in that they regard the array of arrays as an error rather than the normal output of any significant spreadsheet calculation.
In the attached I have used some complicated formula coding to work around the problem but, in terms of complexity, it makes the basic Lambda function look like a walk in the park. The only good things to be said for my solution is that, in use, it works in a straightforward manner and that most of its complexity is hidden from the user/developer. The bad thing is that I suspect there will be very few developers worldwide that are capable of supporting the code (some are on this forum, though).