INDEX, MATCH-Excel

[sudhainfo]

Using the Excel formula, I am trying to find values and match values with previous occurrences. The total repetition of values is fetched from another table using vlookup function. Along with this another condition is added to check if the time difference in match value is within 1 hour limit. If it is less than one hour, the value can't be repeated but if it is greater than 1 hour. the value can be repeated.

I used the formula
=IFERROR( INDEX(D4:F4, MATCH(1, (COUNTIF(G$1:$G3, D4:F4) <= VLOOKUP(D4:F4, J$2:K$7, 2, FALSE)) * (LEN(D4:F4) > 0) * ( (COUNTIFS(G$1:$G3, D4:F4) = COUNTIFS($G$1:$G3, D4:F4)) * (IFERROR(MATCH(D4:F4, G$1:G3, 0), 0) >= 0) * (IFERROR(MATCH(1, (ABS(A4 - INDEX(A$1:A3, MATCH(D4:F4, G$1:$G3, 0))) > TIME(1, 0, 0)), 0), 0) >=0) ), 0) ), "")

In this, row 3 should have value of either 328 or d5e because 13a is found in g1 and also time difference between a3 and a1 is less than 1 hour.

Any help to fix this formula is greatly apprecialted.

 

[GuzmanServices]

Here's my best guess at answering your question, assuming your file has no Column headers and the desired result is entered in Column M.
Please see my formula for Cell M2, which can be copied to produce the following result. Let me know if you have any additional feedback!

=IF($A2-$A$1>=1,$D2,IF(COUNTIFS($G$1:$G2,$D2)=1,$D2,OFFSET($D2,0,1)))