Problem using VLOOKUP with calculated values - for dissertation!

[ljohnston86]

Hi, I'm trying to use the VLOOKUP formula at the moment, and having a few problems. I have a very long list of dates in a main dataset, and I need to calculate the sunrise time that occurred on each date. I have a separate worksheet containing all of the sunrise times. As my original date variable also contained times, which I don't need, I extracted the dates only from this variable into a new variable called 'dates'.

However, I can't seem to use VLOOKUP on these calculated values, it says #NA. If I try to replace the date with one I've typed manually, the VLOOKUP formula works fine. I've tried to copy and paste the date values using the 'Paste Special - Values' feature, thus converting it from a calculation to a value only, however this makes no difference. So to clarify - when I have the date 20/07/2007 in the cell, VLOOKUP won't work. If I erase the cell and type the date in exactly the same way, it works. I've tried reformatting the cell to both 'general' and 'date', neither of which works.

Any assistance would be greatly appreciated, it's for my dissertation which is due in a month!

 

[VoG]

This should help http://www.contextures.com/xlFunctions02.html#trouble

 

[Excelestial]

When you extract your dates from your original date variable, use the following to calculate your date as text: =text(date variable,"dd/mm/yyyy"). If you're still encountering NA's...convert the values you're looking up in the same way.

 

[ljohnston86]

Thank you both so much for your help! Turns out when I checked the length of the two fields (in main dataset and in VLOOKUP table), they were different, so no wonder it didn't work!

 

[Excelestial]

Oh...you were out of range. <smile>