Index Match formula does not look-up/display data all the time.

I'm basically making a spreadsheet that displays the employees that will be on any off-phone activity at any given 15 minute interval. I initially tried a variation of VLOOKUP but I ended up getting duplicated results so I researched and found INDEX MATCH.

Initially, it worked great.

Until I discovered that it doesn't display the data all the time.

I'd select an interval that I can see has off phone activity but it won't display the names or the activity. Swapping the interval/time with a letter fixes it in the interim but that's it. I tried displaying it in one spreadsheet and in smaller increments (broken up in four hour chunks, currently) but it really hasn't resolved the issue.

And I maybe using Google wrong but it hasn't helped either.

Sheet 4 contains the super-simplified data sheet.

When it works:
Works.png


When it does not work:
Notwork0.png
Notwork1.png

Thank you.

Another approach:

=IFERROR(INDEX(J$2:J$124,SMALL(IF(TIME(HOUR($I$2:$I$124),MINUTE($I$2:$I$124),1)=TIME(HOUR($C$4),MINUTE($C$4),1),ROW(J$2:J$124)-ROW(J$2)+1),ROWS($A$1:A1))),"")

Originally Posted by humdingaling

your working is accurate but the DV is not exactly the same

14:30 in shows
in C4 = 0.604166666666666
in column I = 0.604166666666667

i rejigged your file so 14:30 works

Great catch, humdingaling!

Wait, whut? It was that simple? A matter of decimals?

Good gracious diety. >_< I'm going to have to make a macro for that or something. T_T

Much thanks from me to you, ser. That explains it.

Originally Posted by azumi

Another approach:

=IFERROR(INDEX(J$2:J$124,SMALL(IF(TIME(HOUR($I$2:$I$124),MINUTE($I$2:$I$124),1)=TIME(HOUR($C$4),MINUTE($C$4),1),ROW(J$2:J$124)-ROW(J$2)+1),ROWS($A$1:A1))),"")

I'm asking a stupid question, if I may.

How exactly does your proposed string works?

Thank you.

Yeah, @humdingaling. I ended up using what you taught and part of @azumi's formula (TIME). Ended up with two extra columns but I've got the spreadsheet that I want. ^_^

Thanks much for the assist.