SOLVED

# vlookup between two times and return a corresponding value

Occasional Contributor

# vlookup between two times and return a corresponding value

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

# Re: vlookup between two times and return a corresponding value

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

Sheet2:

# Re: vlookup between two times and return a corresponding value

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

# Re: vlookup between two times and return a corresponding value

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.

# Re: vlookup between two times and return a corresponding value

I have no idea how to do that...

# Re: vlookup between two times and return a corresponding value

I don't know why it doesn't work on my spreadsheet, but works for you. Any ideas?

# Re: vlookup between two times and return a corresponding value

``=ISNUMBER(G4)``

# Re: vlookup between two times and return a corresponding value

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)

# Re: vlookup between two times and return a corresponding value

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.

# Re: vlookup between two times and return a corresponding value

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 (Occasional Contributor)
Solution

# Re: vlookup between two times and return a corresponding value

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.

# Re: vlookup between two times and return a corresponding value

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

# Re: vlookup between two times and return a corresponding value

You are welcome, glad it helped