Forum Discussion
Find Common Values In ALL 5 Columns With Array Formulas
Amlesh7400 Perhaps easiest with Power Query. In the attached workbook you'll find a sheet called "Count". I trust you can do your analysis from there.
Thank You. My skills in Excel are limited to using basic formulas so would like to know whether I have to input this "policy" (looks like AWS type policy to me) in power query? or is the "LET" formula to be created?
The formulas are straightforward worksheet formulas (OK, perhaps not so straightforward). The LET function, and now the LAMBDA function (available within the beta channel), are somewhat 'work in progress' that is building towards Excel as a full-blown software development platform!
I hope I have not created too much confusion; I realise that this is not what a typical Excel user expects to see!
Notes: the open and closed circles are conditional formats designed to highlight 'count=5'.
The small filtered table uses a Lambda function to 'hide' the calculation complexity.