Use time as a criteria.... but with +/- 5%

[RockandGrohl]

Hi guys, I'm using the lovely mutli-critiera Index-Match as seen here:

Excel formula: INDEX and MATCH with multiple criteria | Exceljet

In one column I'm matching Date to date, in another column I'm matching currency value to value, and in the third criteria I need to match time.

The problem is that the two systems recorded time with slightly different values, so 05:12:24 on one is 05:13:43 on the other - they never appear to be more than 3 minutes out.

In this particular set of data, it's very, very rare that two users will have the same transaction value on the same date and within the same timeframe (3 mins or so), so I'd like the index-match to positively match someone where the date, value and time matches, but within +/- 5% of the time. (which should be 3 minutes, right?)


Thanks!

 

[Phuoc]

Like this?

=INDEX(E5:E11,MATCH(1,(B5:B11=H5)*(C5:C11=H6)*(D5:D11>=H7-TIME(0,2,0))*(D5:D11<=H7+TIME(0,2,0)),0))}