Forum Discussion
Trying to use a spill range as a reference for FILTER that uses INDEX
Hi all! 👋😊
I am trying to find a solution that will let me use the values in a spill range to query a table and return related values from another column. I attempted to do this using this formula: FILTER(INDEX(Table1,,7),INDEX(Table1,,9)=$F3#,"") - where $F3# is the spill range, its values are in column 9 of Table1 and the required value is in column 7 of the same table. The formula results in an error and I was unable to resolve it to a working state.
Instead, I went for a clunky but simple query that references a bunch of rows where the spill will occur. It fetches the values that I need but is not dynamic... ideally the solution would expand and contract with the spill range. I'm sure it's possible, but just beyond the reach of my current skill set or knowledge so created a sample workbook with dummy data, hoping it might help someone suggest a more elegant solution 🤞
Thanks in advance, Andy!
=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?
=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?
Amazing! It does, thank you! While you were solving that, believe it or not I was trying to find some online resources to help me learn LAMBDA... I have zero experience with it. My workplace only moved up to 365 recently, and while there's a bunch of "new" stuff that I've got across and use regularly, there's probably more that I haven't and don't - including things like LAMBDA that I have zero concept of.
I need to test it in the actual file ASAP to see how it functions there, and importantly do a bit of study so that I can understand your solution. I'll leave my post open for the rest of the weekend to see what, if any, other approaches come though. Thanks again, Oliver 😊
EDIT / UPDATE: I copied the formula to my proper file, updated its references, and it worked exactly as required 🚀
Insert_Key alternatively:
=FILTER(Table1[Power (kW)],ISNUMBER(XMATCH(Table1[Combined Values],F3#)),"none")and feel free to add the SORT() UNIQUE() but the point is another way to do the filter without the LAMBDA but you should definitely learn about LAMBDA and the helper functions
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).
