Forum Discussion
Trying to use a spill range as a reference for FILTER that uses INDEX
=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
m_tarler :
Thank you - this is great! 🤗 I am *much* closer to understanding this than the LAMBDA solution.
While I acknowledge that I need to get across LAMBDA, I feel like I need to prioritise investing in some structured study to get across all of the formulas and functionality that were not included in / supported by Excel 2016. My workplace only moved to 365 a few months back, and I'm becoming increasingly aware that there is a lot that "I don't know that I don't know". I only saw XMATCH for the first time this weekend - will get across how your solution works before releasing it into the wild!
Coming to grips with new functionality and formulas is something I can do in manageable bite-sized chunks but learning LAMBDA feels like it would require much more of a deep-dive.
Do not try to avoid Lambda functions . I could argue that any calculation of significance should be captured as a named Lambda function. The name tells the user (or, come to that, the developer) what the formula is trying to do and the parameters identify precedents. Changing those applies the calculation to a different dataset, in the present case, another model of car.
I have stepped through, bottom-up, in the attached workbook. It is also possible to work top-down. That is, starting by declaring the Lambda function and then filling in the detail until it finally does what it claims.
Thank you, PeterBartholomew1, for your insight and encouragement to tackle LAMBDA, and especially for the time and effort you have invested in capturing the steps taken to give me some idea of what's involved. Much appreciated 😊
