Excel Filter Function (Microsoft 365)

[Sarravana Kumar]

Hello All,

I need help with the Filter Function. I have attached the sample data for reference with the current result & expected results.

Thank you in advance,

 

[petter_hoang]

Try:
=FILTER(B3:E12,MMULT(COUNTIF(H2:H3,D3:E12),{1;1})=COUNTA(H2:H3))

 

[Peter Bartholomew]

This version doesn't create a formula so much, it more aims at creating a 'solution' at sheet level using Lambda.

Filterλ(table, drivers)
= LET(
    driverFields, TAKE(table, , -2),
    criteriaMatched, BYROW((driverFields = drivers) + ISBLANK(drivers), Andλ),
    FILTER(DataSource, criteriaMatched)
  )

 

[Sarravana Kumar]

Hi Peter,

Thanks a ton, this works like magic.

I am trying to learn the Lambda function through youtube, in the interim could you pls. help me how the code will change if I have five variables instead of 2 in Drivers.

Earlier was only Job Name & Division, I need to add may be another 3 like Manager, Location, Management Level.

Thank you again.

 

[Peter Bartholomew]

Assuming the driverFields are still the rightmost fields of the Table you simple select a larger number in the definition which is the first line of the Lambda function, e.g.

Filterλ(table, drivers)
=LET(
    driverFields, TAKE(table, , -3),
    criteriaMatched, BYROW((driverFields = drivers) + ISBLANK(drivers), Andλ),
    FILTER(DataSource, criteriaMatched, "No matches")
)

More effort will go into the validation lists than the code! If the fields do not form a contiguous block to the right of the Table you would need a slight change of strategy and use CHOOSECOLS rather than TAKE to identify the driverFields.

 

[Sarravana Kumar]

Thanks a ton Peter.