find a value in multiple rows

[pedram]

Hi All this is my problem...


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]leave start[/TD]
[TD]leave end[/TD]
[TD]leave one day only[/TD]
[/TR]
[TR]
[TD]smith
[/TD]
[TD]2/8/14[/TD]
[TD]6/8/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]paul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]smith[/TD]
[TD]9/10/14[/TD]
[TD]14/10/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]paul[/TD]
[TD][/TD]
[TD][/TD]
[TD]3/9/14[/TD]
[/TR]
[TR]
[TD]smith[/TD]
[TD]8/12/14[/TD]
[TD]14/12/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]5/12/14[/TD]
[TD]8/12/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jack[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.