# find a value in multiple rows

#### pedram

##### New Member
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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.

THanks for introducing me to array formulas , works great.

Replies
4
Views
184
Replies
1
Views
204
Replies
3
Views
97
Replies
6
Views
404
Replies
3
Views
238

1,203,760
Messages
6,057,202
Members
444,913
Latest member
ILGSE

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back