VLookup to return value for time hh:mm not working past midnight

[RayRay248]

Hi there,

I have spend a long time trying to figure this out and struggled to find anything online... This is the first time I have posted something on here, so thanks in advance for any help!

Basically I have 2 columns of data:
Col A --> minute by minute data (hh:mm format, military time)
Col B --> corresponding temperature value for this time (may be to +2dp)

I have a separate table in which i can enter a time value hh:mm (cell E5) and then the next cell will return the corresponding Temp Value using the formula:

=VLOOKUP(E5,$A$2:$B$32,2)

HOWEVER, this formula works great for times up to 23:59 but returns #N/A for times >= 00:00

I need this to be able to work over midnight. I'd appreciate any help!

Thanks!

[Olly]

Make sure all your time lookup cells (e.g. A2:A32) as well as your value cells (e.g. E5:E9) are both formatted and input as time values. In your attachment, data from A18 (00:01) down is entered as date and time rather than just time, so doesn't match the lookup value.

Change your formula to look for an exact match:

Please Login or Register  to view this content.

[Pete_UK]

Is the attached what you are looking for?

Hope this helps.

Pete

[RayRay248]

Wow thank you both for the quick reply!

OllyXLS thanks, I had highlighted the whole columns to make sure they were formatted as hh:mm. I have no idea how A18 became a date, d'oh! I had tried the formula you suggested before, but unfortunately returns all values as #N/A
Thanks very much for your help though!

Pete thank you so much- this works perfectly! In your formula "=VLOOKUP(MROUND(E6,1/24/60),$A$2:$B$32,2,0)" Could you please explain how "MROUND(E6,1/24/60)" and the "0" part works?

Thanks!

[Pete_UK]

The zero at the end of the VLOOKUP function is directly equivalent to the FALSE parameter that Olly put into his formula - it looks for an exact match.

The MROUND function rounds a value to a multiple of the second parameter (rather than ROUND which rounds to a certain number of decimal places). Times are stored in Excel as fractions of a 24-hour day, so 12:00 noon would have the (internal) value of 0.5, and 6:00 am would have a value of 0.25, and so on. Thus the MROUND function given rounds the time to multiples of one minute. As times are fractional, there are rounding errors that can creep in which you may not be aware of, so 0:15, for example, could actually be quarter past midnight plus or minus a small fraction of a second, and as we are looking for an exact match with the 4th VLOOKUP parameter set to 0 or FALSE, there may be occasions when a match does not exist, even though it might look like one exists. I rounded all the values in column A the same way and then fixed those values, so now it should work fine.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[RayRay248]

Hi,

Sorry to re-open this thread! Pete's formula works perfectly- and thanks for the explanation, I have tried to apply this to my worksheet but I am having troubles with the time format, beacuse I am now trying to make the spreadsheet more automated... (I have multiple spreadsheets with different scenarios, and being able to lookup an input value is key to saving lots of time).

I have attached a copy of my new spreadsheet, in which I want to be able to enter any start time, and the columns to update to this accordingly, minute by minute, but the temperature y-values to stay unchanged. All needs to remain in military time. If you refer to columns Q-V, I have applied Pete's formula (col X). In Pete's response, the formulas work perfectly, but when I change the times to a formula as in this recent spreadsheet for automation, (row before + 1 minute, whether that be +"00:01" or +(1/(24*60)) the formula does not work anymore

I have scoured the internet, tried to create other columns with TIMEVALUE, setting times as hh:mm AM/PM, tried True/1 values for the formulas, but nothing works.. All columns have been checked and re-check for number and time (hh:mm) formats, so this shouldn't be an issue.

I believe the problem is because of the times being formlas... Is there a way around this?

Thanks in advance!

[RayRay248]

Spreadsheet attached...

[Pete_UK]

Consider the situation where you start off with zero in a cell, and you repeatedly add 0.3333 (approx. 1/3) to it in successive cells - after 3 additions you would have 0.9999 which is NOT the same as adding 3 lots of 1/3 to the starting cell. This is the same situation, as there is a limit to the accuracy which Excel can store fractional values, and so with repeated addition of a fraction which is not totally accurate, then errors can build up.

Also, if you keep adding one minute to a time and go through midnight, then the result is effectively 1 day plus a time, but you format the cells to show only the time element - hence you will never get an exact match if you are looking for times like 00:01.

I've changed your formula in Q3 to this:

=MROUND(MOD(V1,1),1/24/60)

and in Q4 to this:

=MROUND(MOD(Q3+1/24/60,1),1/24/60)

So, I am ensuring that the start time is rounded to the correct minute, and I'm also ensuring that the time does not exceed 1 (which is one day) by means of the MOD function. After copying this down column Q and setting midnight to zero, I used Copy/Paste Special/Values into column N to ensure that those times were correct, and now the formulas in column X return the expected values.

You can't really use the fourth VLOOKUP parameter set to TRUE in this case, as the times are not correctly sorted, so the formula in column Y gives spurious results.

Hope this helps.

Pete

[RayRay248]

Pete, this is perfect!

I couldn't figure out another way to modify the times like this. The advice about excel and it's calculation with time and midnight crossover is most useful.


Thank you so much- again!

[Pete_UK]

Well, that's good - you can mark the thread as solved again.

Pete

[zerocool2311]

Hi All

Sorry now I need to ask someone to look at this for me.

I have tried the first option of MROUND but can not get that to work either.

Please can someone see if i am missing anything, I have check that the cells are all formatted the same.

Thank you for all your help

Gavin

[Pete_UK]

Gavin,

Welcome to the Forum.

Please read through the Forum Rules at the top of the screen, particularly Rule 02 which states that you should not hijack someone else's thread. Instead, you should start your own thread - include a link back to this thread if you think it will help.

Pete

[koypayne]

I just created an account so I could reply to this post. This solved a very frustrating issue I'd been working on for two straight hours on a Sunday night. Thank you much!

[FDibbins]

I just created an account so I could reply to this post. This solved a very frustrating issue I'd been working on for two straight hours on a Sunday night. Thank you much!

Always rewarding to see older threads still helping members today - thank you for your input

[Pete_UK]

Wow, that's an old thread. I'm glad it was useful to you, Koypayne.

Pete

[Lloydexcel]

I had a similar problem but different. I have a column of cells formatted to TIME. When I sum up the cells the bar at the bottom shows the total shows up for example as 29:10:00. However the cell with the SUM formula shows 5:10:00. It subtracts 24 hours and gives the remaining value. Found out that you need to format the cells with time and then choose the type --> 37:30:55. This will display hours more than 24.