VLOOKUP keeps returning N/A Value

[HarryWragg1]

Hi,

I am currently trying to sync up two sets of data via a VLOOKUP for 15 minute data discharge data and hourly precipitation data using dates and times e.g - 30/06/2007 16:00:00.

When I do my VLOOKUP, the results returned are N/A. However, when I click into and open the date and time cell and then click enter, the VLOOKUP seems to work and then displays the correct number in the cell next to it.

I have checked the VLOOKUP thoroughly and there seems to be nothing wrong with the equation so my only suggestion is that there might be a formatting error?

I checked to make sure there are no extra spaces in the custom date and time format i have both sets of data and they seem to match up perfectly.

Any help would be great.

Many thanks, Harry

 

[mole999]

have you got calculations turned of, enableevents = false, worksheet changes in code

 

[AlphaFrog]

If you imported or pasted your data from a text source, a common occurrence is that Excel may see the dates in the data as text and not as serial dates. Then if you edit a text-date, that edit (even if you didn't make a change) cooerces it into a serial date and the VLOOKUP formula then can match it.

There are a couple of methods that can coerce all the text-dates into serial dates;

• Select the Dates column and use the Replace feature (Ctrl+H). Replace a / with a / and Replace All
• Use the Text To Columns function