Forum Discussion

asmoore's avatar
asmoore
Copper Contributor
Aug 15, 2025

VLOOKUP Not Working with Time Values

I'm trying to use VLOOKUP to return a time period name (i.e., "Morning," "Afternoon," "Night," etc.) for corresponding time values.

 

The formula works when I manually type the time value as I did in D2 below:

 

However, for the time values in column A, VLOOKUP won't work. The formula is constructed exactly the same (see screenshot below). The values in column A are all Time values, and the values in my lookup table (H1:I:6) are all Time values. I'm at a loss as to why the VLOOKUP formula works when I manually type the time, but it doesn't work with the already existing time values in column A. They're all formatted as Time values. 

 

I appreciate any help or advice! Thank you.

 

excel
Excel on Mac
Formulas and Functions

4 Replies

Sort By
  • Brijeshsharma's avatar
    Brijeshsharma
    Copper Contributor
    Aug 18, 2025

    Hello , your vlookup Formula is correct, check the Cell format of A column ( Start time)  and Set to time format i.e HH:MM:SS AM PM. 

    Image for Reference

    and then write formula
    =VLOOKUP(A2,$H$1:$I$6,2,TRUE)

    Thank

  • Brijeshsharma's avatar
    Brijeshsharma
    Copper Contributor
    Aug 18, 2025

    Hello , your vlookup Formula is correct, check the Cell format of A column ( Start time)  and Set to time format i.e HH:MM:SS AM PM. 

    Image for Reference

    and then write formula
    =VLOOKUP(A2,$H$1:$I$6,2,TRUE)

    Thankyou

  • OlufemiO's avatar
    OlufemiO
    Brass Contributor
    Aug 16, 2025

    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!




     

Resources