Forum Discussion
Solved
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...
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.
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)
- 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.
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.
