Referencing Date/Time In a Lookup

[GB]

Hi, here is an interesting problem... when referencing a date/time calculation and performing a vlookup to another table in some cases the item returned is correct and in other situations it is #N/A. When comparing the cell value in cell B5 and the values in the lookup table they match exactly so I can't figure out why this inconsistency in the result in cell C5. Have a look at the example provided.


https://dl.dropbox.com/u/60464004/Excel/DateTimeLookup.xlsx


Thanks

GB

 

[Hui]

GB


I suspect it is rounding errors ?


But changing C5 to:

=VLOOKUP(B5,$A$14:$B$20,2,1)

fixes it

 

[GB]

Hi Hui,

thanks you are right it does fix it and I can work with that solution, but I checked for rounding by writing a formula and found no differences? Anyway its OK and appreciate your help.


regards

GB

 

[NARAYANK991]

Hi ,


INDEX / MATCH works correctly.


=INDEX($B$14:$B$20,MATCH($B$5,$A$14:$A$20,0))


So does replacing the formula in B5 , which at present is :


=B2+B3-(B4/24)


by this ( with the additional parentheses ) :


=B2+(B3-(B4/24))


Narayan

 

[GB]

Hi Narayan,

thanks that solves a mystery. It was puzzling me for a while.

cheers

GB