find a value in multiple rows

[pedram]

Hi All this is my problem...

name	leave start	leave end	leave one day only
smith	2/8/14	6/8/14
bob
paul
smith	9/10/14	14/10/14
john
paul			3/9/14
smith	8/12/14	14/12/14
bob	5/12/14	8/12/14
jack

<tbody>
</tbody>

the above table is a small sample of a much bigger table.
I would like to have a formula that answers these questions.. ( they are just examples of dates , i hope to be able to use any date in the year)

Is smith on leave on the 10/12/14 ? True
Is Paul on leave on 3/9/14 ? True
Is bob on leave on 10/12/14 ? false

Your help would be appreciated.

 

[wigi]

Hello

If you put the table in A1:D10, the name in cell G1 and the date in cell H1, this works:

=SUM((A2:A10=G1)*(B2:B10+D2:D10<=H1)*(C2:C10+D2:D10>=H1))>0

You need to press Ctrl-Shif-Enter instead of Enter, to confirm the formula. It's an array formula.

 

[pedram]

THanks for introducing me to array formulas , works great.