Look up a score by ID and a +/- 5 min time range

[jakext]

I have 2 spread sheets
A sheet contains subject ID and subject entry time.
B sheet contains the score of each subject.

I want to match a score from B sheet on to A sheet that is at least within 5mins of the entry time. I've tried vlook up and sumproduct formula but that only seem to find exact time match? is there a hidden formula out there that can do the trick of matching up subject ID then look for a score that is within a time range?


Any help would be greatly appreciated!

 

[json]

is it possible to have a testID? there are a couple issues that i'm seeing, one of them is 1 person taking multiple tests. since their subjectID repeats so often, it does not give a unique identifier.... now we could use the Date/Time fields to help identify each test, but theres some formatting issues at hand that are making that difficult. (the "Score Time" field is starting with a 0 while that 0 is not in the "Subject Dose Entry Time" field...


also, perhaps its just me but will the Subject Dose Entry Time and the Score Date and Time fields will always be the same, right?

 

[NARAYANK991]

Hi ,

Check the file.

Narayan

 

[NARAYANK991]

Hi ,

With reference to my uploaded file , you can simplify the formula in P2 from :

=IF(O2=0,"Null",INDEX(Score_Date,IF(ISNUMBER(N2),N2,O2))+INDEX(Score_Time,IF(ISNUMBER(N2),N2,O2)))

to :

=IF(O2=0,"Null",INDEX(Score_Date+Score_Time,IF(ISNUMBER(N2),N2,O2)))

Narayan

 

[jakext]

I figured it out, I just changed index(score_date-->score).

Thank you so much for your help!!!

 

[Somendra Misra]

@jakext
Hi,

Although @NARAYANK991 Sir had given you a very nice solution,I just tried to find a solution of this with less cells involved. So, Just posting another solution: This will not involve helper columns + this is an array formula (so need Ctrl+Shift+Enter) + this is BIG .

=IFERROR(INDEX(B_Score!$B$2:$B$28,SMALL(IF(A2=B_Score!$A$2:$A$28,IF(('A_Entry Time'!B2=(B_Score!$C$2:$C$28+B_Score!$D$2:$D$28))+(('A_Entry Time'!B2>=((B_Score!$C$2:$C$28+B_Score!$D$2:$D$28)-(5/(60*24))))*('A_Entry Time'!B2<=((B_Score!$C$2:$C$28+B_Score!$D$2:$D$28)+(5/(60*24))))),ROW(B_Score!$D$2:$D$28)-ROW(B_Score!$D$2)+1)),1)),"Null")

Just Check for various test data.

Regards,

 

[jakext]

I think both are great solutions which have solve a lot of headache for me.
thank you both!!
To go one step beyond, what would be the best way to make the equation dynamic if I have a month report that is this format but has different rows?