SOLVED

vlookup between two times and return a corresponding value

%3CLINGO-SUB%20id%3D%22lingo-sub-2679816%22%20slang%3D%22en-US%22%3Evlookup%20between%20two%20times%20and%20return%20a%20corresponding%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679816%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20lookup%20values%20between%20two%20values%20to%20return%20a%20corresponding%20result.%26nbsp%3B%20I%20have%20a%20time%20value%20of%2000%3A00%3A23%20in%20cell%20G4%20and%20want%20to%20create%20a%20formula%20to%20make%20it%20so%20that%20any%20time%20value%20between%2000%3A00%3A00%20and%2001%3A59%3A59%20returns%20a%20value%20of%20%220000%20-%200200%22%26nbsp%3B%20and%20then%20value%20between%2002%3A00%3A00%20and%2003%3A59%3A59%20returns%20a%20value%20of%20%220200%20-%200400%22%20and%20so%20on.%26nbsp%3B%20I've%20created%20a%20table%20on%20a%20separate%20worksheet%20and%20tried%20to%20use%20the%20following%20formula%20but%20get%20a%20%22%23N%2FA%22%20error.%3C%2FP%3E%3CP%3E%3DLOOKUP(2%2C1%2F(Sheet2!%24A%242%3A%24A%2413%26lt%3B%3DG4)%2F(Sheet2!%24B%242%3A%24B%2413%26gt%3B%3DG4)%2CSheet2!%24C%242%3A%24C%2413)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2679816%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2679888%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20between%20two%20times%20and%20return%20a%20corresponding%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679888%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127308%22%20target%3D%22_blank%22%3E%40Andrea_Schutt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20to%20have%20sample%20file%20or%20at%20least%20screenshot.%20If%20copy%2Fpaste%20your%20formula%20it%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20482px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305386i3E2A910DA23D586A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESheet2%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20182px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305387i380237682C44DC73%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2679937%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20between%20two%20times%20and%20return%20a%20corresponding%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20are%20the%20screen%20captures%20of%20the%20data%20I'm%20working%20from.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680045%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20between%20two%20times%20and%20return%20a%20corresponding%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127308%22%20target%3D%22_blank%22%3E%40Andrea_Schutt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20a%20different%20strategy%20using%20365%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%20LET(z%2C%20%2200%22%2C%0A%20%20%20%20lower%2C%202*INT(HOUR(time)%2F2)%2C%0A%20%20%20%20upper%2C%20lower%2B2%2C%0A%20%20%20%20uTxt%2C%20%20TEXT(upper%2C%20z)%20%26amp%3B%20z%2C%0A%20%20%20%20lTxt%2C%20%20TEXT(lower%2C%20z)%20%26amp%3B%20z%2C%0A%20%20%20%20lTxt%20%26amp%3B%20%22%20-%22%20%26amp%3B%20uTxt%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20idea%20to%20to%20calculate%20the%20time%20range%20directly%2C%20rather%20than%20using%20a%20lookup.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680067%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20between%20two%20times%20and%20return%20a%20corresponding%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680067%22%20slang%3D%22en-US%22%3EI%20have%20no%20idea%20how%20to%20do%20that...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2680095%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20between%20two%20times%20and%20return%20a%20corresponding%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2680095%22%20slang%3D%22en-US%22%3EI%20don't%20know%20why%20it%20doesn't%20work%20on%20my%20spreadsheet%2C%20but%20works%20for%20you.%20Any%20ideas%3F%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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