Forgot to say,
Then just fill it down.
To partly explain- this is an array formula, which means it does a loop through the ranges, performing a certain formula on each row in the range- so taking only cell F2 for example, it will loop from rows 2 through 6 (because those are the rows that the different ranges referenced in there range between), and will evaluate the part of the formula which is inside the MAX function for that row. The results are all passed back to the max function, then the rest of the formula continues normally. Like this-
Loop from rows 2 to 6:
(A2=A$2)*(B2>=B$2)*(B2<=C$2)*(ROW()<>ROW(A$2)),(A2=A$2)*(C2>=B$2)*(C2<=C$2)*(ROW()<>ROW(A$2)) Result : 0 (see below for explanation)
(A2=A$3)*(B2>=B$3)*(B2<=C$3)*(ROW()<>ROW(A$3)),(A2=A$3)*(C2>=B$3)*(C2<=C$3)*(ROW()<>ROW(A$3)) Result : 0
(A2=A$4)*(B2>=B$4)*(B2<=C$4)*(ROW()<>ROW(A$4)),(A2=A$4)*(C2>=B$4)*(C2<=C$4)*(ROW()<>ROW(A$4)) Result : 1
(A2=A$5)*(B2>=B$5)*(B2<=C$5)*(ROW()<>ROW(A$5)),(A2=A$5)*(C2>=B$5)*(C2<=C$5)*(ROW()<>ROW(A$5)) Result : 0
(A2=A$6)*(B2>=B$6)*(B2<=C$6)*(ROW()<>ROW(A$6)),(A2=A$6)*(C2>=B$6)*(C2<=C$6)*(ROW()<>ROW(A$6)) Result : 1
These values are returned back to the MAX function-- MAX(0, 0, 1, 0, 1) which returns 1 of course. Then to finish up, CHOOSE(1+1,"No","Yes") returns Yes.
Explanation of loop results:
Each evaluation test within the formula, like (A2=A$2) will simply return a zero if false or a one if true. So this is a way of doing a logical AND for the specified criteria. These are the criteria we are 'AND'ing together :
First parameter of the MAX :
- the name matches (A2=A$2)
- start date is between the start and end dates of the row being compared to (B2>=B$2)*(B2<=C$2)
- we are not comparing the row to itself (ROW()<>ROW(A$2))
Second parameter of the MAX :
- the name matches (A2=A$2)
- end date is between the start and end dates of the row being compared to (C2>=B$2)*(C2<=C$2)
- we are not comparing the row to itself (ROW()<>ROW(A$2))
Having the two parameters to the MAX function is like doing a logical OR between them, since the results are 1=true, 0=false.
If you really want to know how this all works, read through this a few times.