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!
Bookmarks