Forum Discussion
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)
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.
12 Replies
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.
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...
Better to have sample file or at least screenshot. If copy/paste your formula it works
Sheet2:
- I don't know why it doesn't work on my spreadsheet, but works for you. Any ideas?
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)
here are the screen captures of the data I'm working from. Thank you!
