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.

 

[steve the fish]

That would produce whatever is in Goals!A12 as written. Check to see what this produces =E9=C12

 

[DDT~123]

That would produce whatever is in Goals!A12 as written. Check to see what this produces =E9=C12

I removed the ">" from the formula and it returned an error.

 

[steve the fish]

So what does =E9=C12 produce? Just paste it into a cell in the goals sheet.

 

[DDT~123]

So what does =E9=C12 produce? Just paste it into a cell in the goals sheet.

It returned as "FALSE"

 

[steve the fish]

Then the value in C12 is probably greater than "0:08:02". Lets try this =ISNUMBER(C12). What does that produce?

 

[DDT~123]

Then the value in C12 is probably greater than "0:08:02". Lets try this =ISNUMBER(C12). What does that produce?

That returned as "TRUE"

 

[steve the fish]

Ok so you have true numbers for your times. Your formula is doing what you are asking it. Its just that E9>=Goals!$C$12 produces false so in other words C12 is greater than E9. C12 probably has either a date component or some milliseconds. This formula could confirm which:

=TIME(HOUR(C12),MINUTE(C12),SECOND(C12))=C12

 

[DDT~123]

Ok so you have true numbers for your times. Your formula is doing what you are asking it. Its just that E9>=Goals!$C$12 produces false so in other words C12 is greater than E9. C12 probably has either a date component or some milliseconds. This formula could confirm which:

=TIME(HOUR(C12),MINUTE(C12),SECOND(C12))=C12

You're good! This returned as FALSE

 

[steve the fish]

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)