Index match not working for time matches consistently

Hi people,

I've gone around in cycles doing this. In column H, I have a formula using the index match functions. The format of the lookup value and the lookup array appear the same.
It would be wonderful is someone could get this to work for me.

Thanks in advance
indexmatch.xlsx

You can change the formula in H2 to this:

=CELL("address",INDEX($J$1:$AG$1,MATCH(F2+TIME(0,0,1)+(F2<TIME(6,0,0)),$J$1:$AG$1,1)))

then copy it down. The additions to the formula are shown in red and blue. The red additions add 1 second to the time in F2 and reduce the search to an approximate (less than) match. As times are stored internally in Excel as fractions, it can be difficult to achieve exact matches, so this technique improves the success rate of the matches. The blue additions take account of your times on row 1 starting with 6:00am, and continuing beyond midnight - instead of 1:00am or 2:00am, those values are actually 25:00am and 26:00am etc., so the blue amendment just adds 1 on for time values below 6:00am.

Hope this helps.

Pete

Fantastic. It worked. Much better than ChatGPT

Thanks for that. I'll keep that as plan B

Glad to help, and thanks for the rep.

Pete