I have 2 databases that I am working with in Excel. They are Internet speed tests which separate the user entered address and the actual speed-test into 2 databases. The address table and the speed-test table don't have a unique identifier to link them together (whoops!), but they do have time-stamps.
I'd like to use the time-stamps as an identifier. How do I do a lookup that tests for time-stamp values that e.g. occur 15 minutes before or after the lookup value.
It would have been helpful if you had provided some sample data. I took a guess and fabricated some data that might resemble yours, and hopefully the explanation of how the formulas work will allow you to adapt this to your data structure.
The main thrust of your question is how to find times in one column that occur within 15 minutes of the times in another column. The Time Stamp columns in the table below are actually complete date-times, but formatted to only show the hours and minutes.
The array formula in E3 is:
=IFERROR(INDEX(D$3:D$52,MATCH(1,(1*(24*60*ABS(B3-D$3:D$52)<=$H$3)),0)),"")
It must be entered with CTRLShiftEnter and then filled down.
Here's how it works: The inner expression ABS(B3-D$3:D$52) produces an array of the absolute differences between the time in B3 and all of the times in Column D. The result is a number where the decimal portion represents the fractional number of days between the two datetimes. Multiplying by 24*60 converts that to minutes, and the inequality checks whether those values are <= the number of minutes in H3 (initially 15, but more about this later).
At this point, the expression (24*60*ABS(B3-D$3:D$52)<=$H$3) produces an array of True/False values corresponding to whether B3 is within 15 minutes of the times in Column D. Multiplying by 1 converts the True/False values to 1's and 0's.
Now the MATCH() finds the position of the first 1 in the array, and INDEX() produces the corresponding time from Column D. Finally IFERROR() produces a blank (instead of #N/A) if no matching time within +/- 15 minutes was found.
This formula in F3: =IFERROR(INDEX(C$3:C$50,MATCH(E3,D$3:D$50,0)),"") simply looks up the Column E time in Column D and returns the corresponding speed from Column C.
Finally, this formula in G3: =IF(SUMPRODUCT(1*(24*60*ABS(B3-D$3:D$52)<=$H$3))>1,"Yes","") checks for errors. It sums up the array of 1's and 0's in the inner expression and produces "Yes" if the answer is > 1. This means there were 2 or more times within 15 minutes of the time in Column B. The MATCH() function will only find the first one, which may not be correct. The way around this is to reduce the value in H3 until the "Yes" disappears from a given row. At that point, the time in Column E is the single closest time to the time in Column B.
I hope you found this helpful.
Notes:
I generated random times for Columns B and D, so there are some times in Column B that have no matching time (+/- 15 minutes) in Column D. If your data has no non-matches, you could remove the IFERROR wrappers.
Presumably your database is growing, so you can use whole column references (e.g. D:D) in the formulas.
