INDEX MATCH lookup if between two times

[DDT~123]

Hello All,

I have the below formula which works if the lookup value is BETWEEN a minimum and maximum value but doesn't work if the value is EQUAL TO a minimum value.


{=IFERROR(INDEX(Goals!$A$12:$A$16,MATCH(1,(E9<=Goals!$D$12:$D$16)*(E9>=Goals!$C$12:$C$16),0)),"")}


Goals!$A$12:$A$16 - This is the range that i want to be output (numeric value)
E9 - The value that i want to lookup (time value)
Goals!$D$12:$D$16 - This is the range which has the maximum values (time value)
Goals!$C$12:$C$16 - This range includes the minimum values (time value)


These are the values i have in Goals!$C$12:$D$16

Min	Max
0:08:02	2:00:00
0:07:41	0:08:01
0:07:20	0:07:40
0:06:59	0:07:19
0:00:01	0:06:58

<colgroup><col><col></colgroup><tbody>
</tbody>

I'm receiving an error when the lookup value in E9 is 0:08:02


Any help would be greatly appreciated.

 

[DDT~123]

Then you have milliseconds in C12 i believe. Try retyping it into the cell and you should see your formula produce a result rather than an error (blank)

That worked! How would I get the proper number without the milliseconds? The cells are formatted with 37:30:55

 

[steve the fish]

Try using this:

=FLOOR(C12,1/86400)

Drag it down and across. Copy it and paste special values over your times.

 

[DDT~123]

Try using this:

=FLOOR(C12,1/86400)

Drag it down and across. Copy it and paste special values over your times.

That worked! Thank you so much for your time on this!!