Vlookup with Dates - undocumented error!

[Sean]

MSExcel Help tells you that "if range_lookup is FALSE,
table_array does not need to be sorted". It also states
that "by default the table must be sorted in an ascending
order"

This is not correct when you are using Vlookup for dates
(mm/dd/yy). I have painstakenly found that the
table_array not only needed to be sorted but needed to be
sorted in DECENDING order. Otherwise, it continued to
return non existing dates. - Almost cost me my JOB!!!

Thanks MS.

 

[Frank Kabel]

Hi Sean
what formula did you use as the following:
=VLOOKUP(DATE(2003,12,12),A1:B1000,2,0)
works well for me if the range A1:B1000 is not sorted in any way

 

[Bob Phillips]

Sean,

That damn Bill Gates, who does he think he is!

Seriously, I think you have got it wrong. Dates are just numbers,
representing the number of days since Jan 1 1900, formatted in a specific
way. Looking at the help, I do not read anything about '... by default the
table must be sorted in an ascending order ..'. The TRUE value is used to
return an approximate value if required and the table must be sorted in
ascending order, FALSE is to get an exact value.

Most likely, you are not using real dates, but fields that look like dates.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

I changed the cell formats from mm/dd/yyy to represent
numbers #####. The same problem occurred.

Another part of the problem was that I had 1500 rows. It
returned 1100 of them correctly. 400 incorrectly.
Everything was formated in the same way.

And if you go to the help, through the formula, if you
read the very first bullet point under "vlookup" it
says "...placed in acending order" however, this is
irrelevant because I used "False" in the rangelookup.

In any event, the only only only way I was able to remedy
this was to sort it decending... I even tried it on a new
PC and got the same result.

 

[Frank Kabel]

Hi Sean
just getting curious. Could you post your exact VLOOKUP formula and
some sample rows of your data (expecially the ones with an error)

 

[Sean]

No problem... But I've found that the file I'm working
with has duplicates with different dates. I'll have to
clean it up, and I'll post another message with the
result. I have a strange feeling that it will be
fine!!... Dammit!!!

VLOOKUP(A1424,'Project Listing - Everything'!A:H,8,FALSE)

Main Worksheet
A B
1 PROJECT# DATE
2 20036 =VLOOKUP(A1424,'Project Listing -
Everything'!A:H,8,FALSE)


Project Listing-Everything Worksheet
A
1 PROJECT# H
2 20036 08/01/2003

 

[Bob Phillips]

And if you go to the help, through the formula, if you
read the very first bullet point under "vlookup" it
says "...placed in acending order"

Yes, but this is not how you originally phrased it, which was '... by
default the table must be sorted in an ascending order ..'. Very different
statments.

In any event, the only only only way I was able to remedy
this was to sort it decending... I even tried it on a new
PC and got the same result.

But I'll wager that it will turn out to be your data, not VLOOKUP.

 

[Sean]

Not to beat a dead horse, but in the second sentance of
the Vlookup Wizard it does say.. "by default the table
must be sorted in an ascending order"

Lets hope its my data and not Vlookup.

Thanks for your participation!