Thread: lookup value in table with start and stop time ranges that don't line up nicely

hello,

was wondering if anyone has a shorter formula for this one that will make the lookups quicker.
the formula i came up with is probably quite clunky resulting in very slow refreshes...

what am I trying to accomplish?
- i have a set of data with start and stop timestamps with each row having a certain vaule associated with it.
- then i have a second set of data with a list of single timestamps.

i basically want to see if a timestamps falls within any of the start/stop time ranges and if so return the value that is associated with that time range.
note that the start/stop timestamps don't line up so there are gaps in between.

example of the data set in Sheet2:
column A start time column B stop time column C value
01/03/2009 07:30 01/03/2009 23:09 AA
02/03/2009 06:00 02/03/2009 11:45 AA
04/03/2009 10:00 04/03/2009 10:59 CC
06/03/2009 05:43 06/03/2009 06:00 DD
06/03/2009 06:00 06/03/2009 11:40 AA
09/03/2009 07:20 09/03/2009 18:38 EE
10/03/2009 04:00 10/03/2009 06:00 PP
10/03/2009 06:00 10/03/2009 12:05 PP
10/03/2009 17:26 10/03/2009 18:00 FF
10/03/2009 18:00 10/03/2009 20:06 GG
10/03/2009 21:18 10/03/2009 23:43 CC
etc

example of the time stamps in Sheet1:
column A
05/03/2009 10:45
05/03/2009 20:00
06/03/2009 07:30
06/03/2009 08:20
06/03/2009 08:30
06/03/2009 09:00
06/03/2009 14:05
07/03/2009 12:00
09/03/2009 08:00
09/03/2009 11:00
09/03/2009 11:45
10/03/2009 08:00
10/03/2009 13:37
etc

the formula i came up with to put in column B in Sheet1 is as follows:

=IF(SUMPRODUCT(--('Sheet2'!A:A<=A1)*('Sheet2'!B:B>A1))=1,INDEX('Sheet2'!A:C,SUMPRODUCT(--('Sheet2'!A:A<=A1)*('Sheet2'!B:B>A1),ROW('Sheet2'!A:A)),3),"No match")

the formula works fine but takes a looooooooong time to run. for one because the list of timestamps i have to run this on is quite large (3000+ rows with start/stop data and 8000+ rows of timestamps).

any suggestions on how to make this lookup more efficient are very welcome.

thanks!

Limit your ranges to the 3000+ instead of the full column ranges. That will speed it up significantly... but if not significantly enough, then try adding a column D to Sheet2 with row numbers starting from D1 enter 1, then hold CTRL key, click and drag the little black square at the bottom right corner of cell D1 down to bottom.

Then use formula in Sheet1:

=IF(COUNTIFS(Sheet2!A:A,"<="&A1,Sheet2!B:B,">"&A1)=1,INDEX(Sheet2!A:C,SUMIFS(Sheet2!D:D,Sheet2!A:A, "<="&A1,Sheet2!B:B,">"&A1),3),"No match")