Vlookup on dates

Hi,

I've got a whole load of prices showing against dates and times in the format '03/01/2005 07:44:59'. I'm trying to use a vlookup on a different sheet to look up certain date and time combinations and return the price in the next column. e.g. =VLOOKUP((C11+$G$1),'Cleaned GLN'!$A$2:$E$169250,5,FALSE)

C11 has '03/01/2005' in it and G11 has '07:44:59'.

When i enter the above formula i just get #NA. When i try changing the 'false' to 'true' i get the previous row's price....

Any ideas?

Thanks very much
Jo

You will get #N/A if there is no exact match (because your 4th parameter for VLOOKUP is set to FALSE), so are you sure that those values are EXACTLY the same? Try setting the format on the cell that you think it matches with to dd/mm/yyyy hh:mm:ss.000 (which will show fractions of a second), and set the format of G11 to hh:mm:ss.000 and see if they are the same.

Hope this helps.

Pete

Thanks. I've checked that - they are formatted exactly the same...

I didn't ask you to check the format - change the format on both so that you have .000 after the :ss, and then see if they show the same values to 1/1000ths of a second.

Pete

Yep, done that. Both showing exactly the same. (i.e. now showing 03/01/2005 07:44:59.000)

Well, they are obviously not matching, so there must still be some very slight difference. I don't think Excel guarantees anything less than 1/1000th of a second, so it might be that you have differences of that small order. On your Cleaned GLN sheet you could think about applying Excel's 'Precision as Displayed' feature once the data is displaying in the format that you wish - note, though, that this is not something that you can undo, so ensure that you do it on a copy of your workbook.

Hope this helps.

Pete

Thank you thank you thank you! That = text thing worked.

It was driving me mad...

Thanks
Jo