Excel Vlookup Dates Problem 1

[scribbler]

Can anyone please tell me what I'm doing wrong . I have a simple 2 colimn list consisting of dates then values my formaula looks like =VLOOKUP(J2,D2:H72,2,TRUE)

and I expect it to lookup the nearest date and return a value. Even when I enter an exact date that I know there is a match to I get the same result #N/A

I've tried formatting the dates differently but still no joy.
I tested the dates by taking date1-date2 and get a correct result so I'm unsure where the problem lies.

 

[GlennUK]

Is your data sorted in ascending order?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[scribbler]

Thanks for replying Glenn

Yes my data is in date order

 

[scribbler]

The dates represent a week commencing on a Sunday and my lookup needs to find the relevant week where the dates/values could be changed 3-5 times per week

 

[GlennUK]

Copy your dates to a new sheet ( both from the list, and from your search value ) and format as General, and see if either has any decimal part ( making it non-integer ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[scribbler]

I did as you suggested and the dates from my list appear ok but the dates from the search value have now displayed as
a numeric value i.e. 01/05/2003 now shows 37742

 

[GlennUK]

The dates from your list should have displayed as numeric too!

It sounds like your list is made up of text strings, and not true Excel dates.

In your data sheet, type a 1 into an empty cell, and do Edit/Copy on the cell, then select your list of dates and do Edit/Paste Special/Multiply. That should fix things.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[scribbler]

Glenn

If I type a 1 into an empty cell then copy from that cell and Edit/Paste Special/Multiply over my dates in my search value list it changes their display from a date to numeric value.

 

[GlennUK]

Simply do Format/Cells/Number and choose a date format that you like.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[scribbler]

Thanks for the replies Glenn

That solved the problem.

Cheers Colin