Lookup if any value in a list is within a list of ranges in a seperate table

[nhaws0204]

Hello, I am trying to write a formula that will tell me if any value in a list of values for a specific user is within a range of values.

For Example I would like to display "Y" in the "Any Projects Time Logged While Working this Request" column of table 1 if any of the values in the "Status End Time" column of table 2 for Availability Status "PROJECTS" fall betwee the range "Time Finished" minus the number of minutes in the "Handle Time in Minutes"

Table 1:

Routing User ID	Time Finished	Handle Time in Minutes	Any Projects Time Logged while working the request?
BABBS2	3/28/2013 10:25:13 AM	15.166667	N
GOARDD1	3/28/2013 11:25:17 AM	10.95	N
BABBS2	3/28/2013 11:45:19 AM	61.583333	N
DANIEK11	3/28/2013 05:07:55 PM	8.77	N
DANIEK11	3/28/2013 01:34:13 PM	5.85	N
BABBS2	3/28/2013 03:58:19 PM	24.85	N
BABBS2	3/28/2013 03:33:18 PM	138.383333	Y
BABBS2	3/28/2013 05:31:55 PM	82.5	Y

<TBODY>
</TBODY>

Table 2:

Availability Status</SPAN>	Availability User ID</SPAN>	Status Start Datetime</SPAN>	Status End Datetime</SPAN>
ADMIN</SPAN>	BABBS2</SPAN>	03/28/2013 12:11:39 PM</SPAN>	3/28/2013 01:36:45 PM</SPAN>
AVAILABLE</SPAN>	BABBS2</SPAN>	03/28/2013 09:50:29 AM</SPAN>	3/28/2013 10:08:23 AM</SPAN>
AVAILABLE</SPAN>	BABBS2</SPAN>	03/28/2013 10:08:23 AM</SPAN>	3/28/2013 10:09:47 AM</SPAN>
PROJECTS</SPAN>	BABBS2</SPAN>	03/28/2013 02:55:17 PM</SPAN>	3/28/2013 03:23:15 PM</SPAN>
PROJECTS</SPAN>	BABBS2</SPAN>	03/28/2013 04:18:37 PM</SPAN>	3/28/2013 05:25:35 PM</SPAN>
PROJECTS</SPAN>	DYSERK</SPAN>	03/28/2013 09:53:28 AM</SPAN>	3/28/2013 10:13:12 AM</SPAN>
AVAILABLE</SPAN>	GOLDENT</SPAN>	03/28/2013 08:02:41 AM</SPAN>	3/28/2013 08:36:28 AM</SPAN>
PROJECTS</SPAN>	GOLDENT</SPAN>	03/28/2013 10:30:09 AM</SPAN>	3/28/2013 11:34:46 AM</SPAN>
AVAILABLE</SPAN>	MCQUOWD2</SPAN>	03/28/2013 10:27:46 AM</SPAN>	3/28/2013 10:44:04 AM</SPAN>
ADMIN</SPAN>	MCQUOWD2</SPAN>	03/28/2013 10:56:29 AM</SPAN>	3/28/2013 12:05:27 PM</SPAN>
AVAILABLE</SPAN>	MCQUOWD2</SPAN>	03/28/2013 04:48:28 PM</SPAN>	3/28/2013 05:45:20 PM</SPAN>
AVAILABLE</SPAN>	WHALENJ</SPAN>	03/28/2013 02:34:21 PM</SPAN>	3/28/2013 02:51:31 PM</SPAN>

<TBODY>
</TBODY>

Any help you can provide is greatly appreciated. I am using Excel 2007. I have highlighted the projects records in table 2 that should result in "Y" records in table 1. Thanks!

 

[jamtay317]

there are so many difffernt times how do we know which one to look up to between?

 

[jamtay317]

this is what I came up with, but its says that there are more "Y" than what you said please tell me the other criteria
=IF(SUMPRODUCT(--(B2>=$C$16:$C$27)*(B2<=$D$16:$D$27)*(A2=$B$16:$B$27))>0,"Y","N")

 

[jamtay317]

this formula does what your looking for, however you have to change the C column into time format by putting "0:" infront of the min


=IF(SUMPRODUCT(--((B2-C2*60)>=$C$16:$C$27)*((B2-C2*60)<=$D$16:$D$27)*(A2=$B$16:$B$27))>0,"Y","n")

 

[nhaws0204]

this formula does what your looking for, however you have to change the C column into time format by putting "0:" infront of the min


=IF(SUMPRODUCT(--((B2-C2*60)>=$C$16:$C$27)*((B2-C2*60)<=$D$16:$D$27)*(A2=$B$16:$B$27))>0,"Y","n")

Thanks Jamtay317! Sorry for the slow response to your initial question. I need the formula to look between only the records with "PROJECTS" in column A of table 2 for the same user ID (column A for table 1 and Column B for table 2.) I am trying to work with formula that you posted and I'll let you know if I can make it work.