SOLVED

vlookup between two times and return a corresponding value

Copper Contributor

I am trying to lookup values between two values to return a corresponding result.  I have a time value of 00:00:23 in cell G4 and want to create a formula to make it so that any time value between 00:00:00 and 01:59:59 returns a value of "0000 - 0200"  and then value between 02:00:00 and 03:59:59 returns a value of "0200 - 0400" and so on.  I've created a table on a separate worksheet and tried to use the following formula but get a "#N/A" error.

=LOOKUP(2,1/(Sheet2!$A$2:$A$13<=G4)/(Sheet2!$B$2:$B$13>=G4),Sheet2!$C$2:$C$13)

12 Replies

@Andrea_Schutt 

Better to have sample file or at least screenshot. If copy/paste your formula it works

image.png

Sheet2:

image.png

 

@Sergei Baklan 

here are the screen captures of the data I'm working from.  Thank you!

@Andrea_Schutt 

I have tried a different strategy using 365

= LET(z, "00",
    lower, 2*INT(HOUR(time)/2),
    upper, lower+2,
    uTxt,  TEXT(upper, z) & z,
    lTxt,  TEXT(lower, z) & z,
    lTxt & " -" & uTxt
  )

The idea to to calculate the time range directly, rather than using a lookup.

I have no idea how to do that...
I don't know why it doesn't work on my spreadsheet, but works for you. Any ideas?

@Andrea_Schutt 

The secret is in your data. And only your know your data.

=ISNUMBER(G4)

 

@Andrea_Schutt 

If it doesn't work only for one cell - hard to say without the file.

If it doesn't work for all cells, perhaps time in column G is entered as text, not as actual time (which is number internally). You may check in any empty cell by =ISTEXT(G4)

That's it. It's a problem with the Time cell (column G) not displaying as a time value. For whatever reason I'm having trouble getting the cell to format correctly as a time rather than text. I discovered that if I delete the first "0" of the two digit hour then it formats, but that is quite time consuming to do manually.

@Andrea_Schutt 

Without 365 the formula I posted 'reduces' to

=  TEXT(2*INT(HOUR(time)/2), "00") & "00" & 
   " -" & 
   TEXT(2*INT(HOUR(time)/2)+2, "00") & "00"

That doesn't help, however, if 'time' is presented as Text.

best response confirmed by Andrea_Schutt (Copper Contributor)
Solution

@Andrea_Schutt 

To convert text to time it's not enough to change format, you need to re-enter the values. Easiest way - apply Time format to column G; select it; Data->Text to Columns-> select Fixed Width->Finish.

Wow! This fixed everything! Thank you so much Sergei!

@Andrea_Schutt 

You are welcome, glad it helped

1 best response

Accepted Solutions
best response confirmed by Andrea_Schutt (Copper Contributor)
Solution

@Andrea_Schutt 

To convert text to time it's not enough to change format, you need to re-enter the values. Easiest way - apply Time format to column G; select it; Data->Text to Columns-> select Fixed Width->Finish.

View solution in original post