VLOOKUP Not Working with Time Values

Hi everyone

After testing several approaches, I found multiple solutions that work depending on how your data is structured. I documented all the methods in Excel and included screenshots to show how each one works. Here's a breakdown of the solutions I implemented:

MOD() to Strip Hidden Date Parts Sometimes time values carry hidden date components that break VLOOKUP. I used:

=MOD(A2,1)

=VLOOKUP(MOD(A2,1), H2:I7, 2, FALSE)

Rounded Time for Precision Matching To eliminate seconds/milliseconds:

=TIME(HOUR(A2), MINUTE(A2), 0)

=VLOOKUP(TIME(HOUR(A2), MINUTE(A2), 0), H2:I7, 2, FALSE)

Approximate Match for Time Ranges I sorted my lookup table by time and used:

=VLOOKUP(A2, H2:I7, 2, TRUE)

IFS() for Direct Categorization For full control without a lookup table:

=IFS(

  AND(A2>=TIME(6,0,0), A2<TIME(12,0,0)), "Morning",

  AND(A2>=TIME(12,0,0), A2<TIME(18,0,0)), "Afternoon",

  AND(A2>=TIME(18,0,0), A2<TIME(21,0,0)), "Evening",

  OR(A2>=TIME(21,0,0), A2<TIME(6,0,0)), "Night"

)

XLOOKUP (Excel 365) For flexible matching and error handling:

=XLOOKUP(MOD(A2,1), H2:H7, I2:I7, "Not Found", 1)

Conditional Formatting for Visual Cues I added color coding to highlight time periods:

• Morning - Light Yellow
• Afternoon -Orange
• Evening - Blue
• Night -  Purple

Used formulas like:

=F2="Morning"

Hope this helps others facing the same issue!