Excel filter formula for multiple criteria and value in array-CodePudding

I'm trying to filter some information from one table but I need the criteria to look for some info inside an array. I'll give an example to try to explain what I need:

I need to get everyone from Array1 (E2:E4) that is listed on TABLE1 (A2:C4) and has Monday and Yes on their respective columns. Hope that makes sense and that somebody will be able to help me on this one.

Thanks a lot!

Tried using filter formula but I can't make it work with the array as a filter.

CodePudding user response：

I agree with user who mention this place is not for homework, but I know useful information about it.

Microsoft explain how use "AND" into filter in the following

Formula: =FILTER(A2:C4, (B2:B4=D6) * ( C2:C4=D7),"")

D6 = Monday D7 = Yes

Some fun fact Fulano, Beltrano and Ciclano are Fulano, Mengano and Sultano in my country.

CodePudding user response：

What about this, it will give you the required output,

• Formula used in cell G2

=FILTER(A2:A4,(MMULT(--(TRANSPOSE(E2:E4)=A2:A4),ROW(E2:E4)^0)=1)*(B2:B4="Monday")*(C2:C4="Yes"))