Need you Help with filter function

[geo Jul]

Dear Expertise

please need your help

I need to filter column B according to I3:J5 and all three conditions for each name must be Met

example

if the name Sam had been filtered the three requirements should have

1: Incentive Level is equal to 3

2: Product Knowledge is equal to 4

3: Personal Skills is equal to 5

I really appreciate any help you can provide.

I am using Excel 2016 and 365 version

 

[AliGW]

I am using Excel 2016 and 365 version

Which? If it has to work with both, them 365 functions such as FILTER can't be used.

 

[AliGW]

Here is a 365 solution:

=LET(t,TEXTJOIN(" ",,J3:J5&I3:I5),c,C3:C32,de,D3:D32&E3:E32,
f,FILTER(c,ISNUMBER(FIND(de,t))),
SORT(UNIQUE(FILTER(f,BYROW(f,LAMBDA(r,SUMPRODUCT((f=r)*1)=3))))))

 

[geo Jul]

Here is a 365 solution:

=LET(t,TEXTJOIN(" ",,J3:J5&I3:I5),c,C3:C32,de,D3:D32&E3:E32,
f,FILTER(c,ISNUMBER(FIND(de,t))),
SORT(UNIQUE(FILTER(f,BYROW(f,LAMBDA(r,SUMPRODUCT((f=r)*1)=3))))))

Thank you so much; I appreciate your help with this problem​

if you please is it possible to replace the LAMBDA function with another as some co-workers are not familiar with this function​

 

[AliGW]

No, sorry - if you specifically did not want the LAMBDA function to be used, you should have said so at the start.

I don't see why it should be an issue when ONLY the ranges in the first line will ever need changing:

=LET(t,TEXTJOIN(" ",,J3:J5&I3:I5),c,C3:C32,de,D3:D32&E3:E32,
f,FILTER(c,ISNUMBER(FIND(de,t))),
SORT(UNIQUE(FILTER(f,BYROW(f,LAMBDA(r,SUMPRODUCT((f=r)*1)=3))))))

Take it or leave it.