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
[TABLE="width: 172"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Min[/TD]
[TD]Max[/TD]
[/TR]
[TR]
[TD]0:08:02[/TD]
[TD]2:00:00[/TD]
[/TR]
[TR]
[TD]0:07:41[/TD]
[TD]0:08:01[/TD]
[/TR]
[TR]
[TD]0:07:20[/TD]
[TD]0:07:40[/TD]
[/TR]
[TR]
[TD]0:06:59[/TD]
[TD]0:07:19[/TD]
[/TR]
[TR]
[TD]0:00:01[/TD]
[TD]0:06:58[/TD]
[/TR]
</tbody>[/TABLE]


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!!