How to find first Monday which meets criteria

[DDT123]

Greetings All,

I have a list of dates in column A and a list of names in column B. I'm trying to find the first Monday in column A if the name in column B equals "John". Next, I'd want to list the second Monday in A if the name in column B equals "John". What would be the formulas for this?

Thanks in advance!!

 

[DanteAmor]

Try:

=FILTER(A2:A100,(WEEKDAY(A2:A100,2)=1)*(B2:B100="John"))

 

[DDT123]

Try:

=FILTER(A2:A100,(WEEKDAY(A2:A100,2)=1)*(B2:B100="John"))

Thank you for responding...I received a #VALUE! error with this formula

 

[DDT123]

Try:

=FILTER(A2:A100,(WEEKDAY(A2:A100,2)=1)*(B2:B100="John"))

Thank you, that worked! I was originally entering "A:A" instead of including start/end row numbers. It worked after I entered a specific range.

 

[Ludwig]

You could also avoid VBA by using the formula:

=INDEX( FILTER( A2:A100, ( WEEKDAY( A2:A100, 2 ) = 1)  * ( B2:B100 = "John" ), "no date matches" ), 1 )

If your data is in a table (or you convert it one), you could use:

=INDEX( FILTER( Table1[Date], ( WEEKDAY( Table1[Date], 2 ) = 1 ) * ( Table1[Name] = "John" ), "no date matches" ), 1 )

 

[Ludwig]

You could also avoid VBA by using the formula:

=INDEX( FILTER( A2:A100, ( WEEKDAY( A2:A100, 2 ) = 1)  * ( B2:B100 = "John" ), "no date matches" ), 1 )

If your data is in a table (or you convert it one), you could use:

=INDEX( FILTER( Table1[Date], ( WEEKDAY( Table1[Date], 2 ) = 1 ) * ( Table1[Name] = "John" ), "no date matches" ), 1 )

I forgot to say:
Increment the number "1" for the index function as required i.e. code "2" to get your second match. You'll need to handle "there isn't a valid match" gracefully of course to avoid a # error value.