Need help with VLookup

[htmldiva]

I'm trying to do a vlookup on time and I just can't get it to work properly. I have to input time in worksheet 1, cell 2 and I need to get the nearest quarter value from a custom lookup table (Sheet1!A1:B17), Column 2:

My lookup table has data in it as follows:

Time Value
8:05 AM 8:00 AM
8:06 AM 8:00 AM
8:07 AM 8:15 AM
8:08 AM 8:15 AM

My formula is =VLOOKUP(C2,Sheet1!A1:B17,2)

When I type 8:07 in cell C2 it returns 8:00 AM not 8:15 AM. What gives?

 

[fairwinds]

Hi,

Try;

=VLOOKUP(MROUND(C2,1/96),Sheet1!A1:B17,2)

 

[Oaktree]

Are you sure the value in Sheet1!A3 (or wherever the 8:07AM is) does not have seconds associated with it? Your vlookup seems to be saying that the 8:07 in C3 is smaller than the 8:07 in Sheet1!A3 (try =C3=Sheet1!A3 to test)

 

[IML]

It should work fine. If you are trying to round to the nearest quarter hour, you could also use
=ROUND(C2*96,0)/96

note that 8:07:29 would go to 8 and 8:07:30 to 8:15, however.

 

[fairwinds]

Maybe I missread...

Maybe just:

=MROUND(C2,1/96)

and skip the table?

 

[htmldiva]

Thanks for the quick response guys. Speaking as a newbie, I don't want excel to round these numbers automatically, I want it to take the data from my lookup table. So if my lookup value for 8:07 is 8:29, that's exactly what I want to reflect, not 8:15 or 8:00.

Should I change the field to text? My other code in VBA works beautifully. I just need help tweaking these numbers.

How do I post the attachment so that you can take a look.

 

[Aladin Akyurek]

Thanks for the quick response guys. Speaking as a newbie, I don't want excel to round these numbers automatically, I want it to take the data from my lookup table. So if my lookup value for 8:07 is 8:29, that's exactly what I want to reflect, not 8:15 or 8:00.

Would you check the cell of 8:07 AM with:

=ISNUMBER(TheCellWhichHousesTheTimeValueOfInterest)

What do you get?

Should I change the field to text? ...

No.

 

[htmldiva]

I get "TRUE"

 

[Aladin Akyurek]

I get "TRUE"

So it's not text.

Next tests:

=COUNTIF(Range,ThatProblemCell)

and

=ThatProblemCell*24

What do we get?

BTW, your sample looks sorted in ascending order on the Time column. That's exactly as needed by the VLOOKUP formula you invoke. But, does the real data meet that requirement?

 

[htmldiva]

I get "0" on both tests.

To create my lookup table, I created two columns and applied a time format to both columns. The data is sorted in both columns in ascending order. The first column contains time entered (08:00 AM, 08:01 AM, 08:02 AM, etc. The second column contains the value if if looked up (any time after 8a and before 8:07a should be whatever I specify, anytime after 8:07a and before 8:15a should be whatever I specify, etc.).

It just won't make 8:07 what I want it to be. Will let you know if I figure out another solution. Thanks again for being so diligent.