Advanced Search problem, No idea to get Through

[Asr007]

I need a formula for searching. Its an assignment:

A customer wants appointment at a specific date and time for a specific Doctor.
Say, For D0004 (Doctor Id) at 21/12/2014 07:00PM

I have another sheet which have list of Past and future appointments with columns "Appointment ID,Doctor Id,Date and time".

The search must check that if the date is not Past and whether desired doctor is available at desired Date & time of customer and must return a Yes or No. It means search should check the doctor ID, date and time in each row and if matches in any row then return a "No" otherwise "Yes".

[TABLE="width: 348"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Doctor ID[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]Apointment[/TD]
[TD]D0004[/TD]
[TD="align: right"]21/12/2014[/TD]
[TD="align: right"]19:00[/TD]
[/TR]
</tbody>[/TABLE]

Available= "Formula ?"

[TABLE="width: 417"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Appointment ID[/TD]
[TD]Doctor ID[/TD]
[TD]Patient ID[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]A0001[/TD]
[TD]D0001[/TD]
[TD]P0001[/TD]
[TD]10/12/2013[/TD]
[TD]10:00 AM[/TD]
[/TR]
[TR]
[TD]A0002[/TD]
[TD]D0002[/TD]
[TD]P0002[/TD]
[TD]11/12/2013[/TD]
[TD]12:00 PM[/TD]
[/TR]
[TR]
[TD]A0003[/TD]
[TD]D0003[/TD]
[TD]P0003[/TD]
[TD]12/12/2013[/TD]
[TD]9:00 AM[/TD]
[/TR]
[TR]
[TD]A0004[/TD]
[TD]D0004[/TD]
[TD]P0004[/TD]
[TD]13/12/2013[/TD]
[TD]7:30 PM[/TD]
[/TR]
[TR]
[TD]A0005[/TD]
[TD]D0005[/TD]
[TD]P0005[/TD]
[TD]14/12/2013[/TD]
[TD]6:30 PM[/TD]
[/TR]
[TR]
[TD]A0006[/TD]
[TD]D0006[/TD]
[TD]P0006[/TD]
[TD]15/12/2013[/TD]
[TD]4:00 PM[/TD]
[/TR]
[TR]
[TD]A0007[/TD]
[TD]D0007[/TD]
[TD]P0007[/TD]
[TD]16/12/2013[/TD]
[TD]7:30 PM[/TD]
[/TR]
[TR]
[TD]A0008[/TD]
[TD]D0008[/TD]
[TD]P0008[/TD]
[TD]17/12/2013[/TD]
[TD]9:00 AM[/TD]
[/TR]
[TR]
[TD]A0009[/TD]
[TD]D0009[/TD]
[TD]P0009[/TD]
[TD]18/12/2013[/TD]
[TD]8:00 AM[/TD]
[/TR]
[TR]
[TD]A0010[/TD]
[TD]D0010[/TD]
[TD]P0010[/TD]
[TD]19/12/2013[/TD]
[TD]7:00 PM[/TD]
[/TR]
[TR]
[TD]A0011[/TD]
[TD]D0009[/TD]
[TD]P0011[/TD]
[TD]20/12/2013[/TD]
[TD]5:00 PM[/TD]
[/TR]
[TR]
[TD]A0012[/TD]
[TD]D0004[/TD]
[TD]P0012[/TD]
[TD]21/12/2014[/TD]
[TD]7:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

i hope i have delivered my question.

 

[bertie]

Hi, and welcome to the forum.

One solution would be to use a SumProduct() function.

Assuming your lookup data is on Sheet2, spanning column A:E and Column E is formatted as time.

Excel 2007

	A	B	C	D	E
Doctor ID	Date	Time
Apointment	D0009

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]Available[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]21/12/2014[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: center"]No[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E2[/TH]
[TD="align: left"]=IF(SUMPRODUCT(--(B2=Sheet2!$B$2:$B$13),--(D2=Sheet2!$E$2:$E$13))=1,"No","Yes")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The first part of the SumProduct:
--(B2=Sheet2!$B$2:$B$13)
Builds an array of ones and zeros (true or false) where a match is found over the given range in Sheet2 for the doctor id. e,g,, {0,0,1,0,etc}

The second part:
--(D2=Sheet2!$E$2:$E$13)
Builds an array where a match is found for the TIME in D2 over the given range in Sheet2. e.g., {0,0,1,0,etc}

Where corresponding array entries = 1, the whole SumProduct() function = True, i.e., a match is found.
Wrap an IF statement around it to test this value and output desired result.

 

[Asr007]

Love you sir!!!!

Its Working!!!

Thank You....