To emphasise what David Smart said
"the area you are looking up must be sorted in ascending order on the lookup column" - That means Oldest first!
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of David Smart
Sent: Saturday, 17 September 2011 7:41 AM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Vlookup(Date, range, 1, true)
If it works with the False parameter and is not giving you the correct results with the True parameter, then the problem is almost certainly in the data you are looking up.
You haven't said whether 1/3/2000 is in your list of lookup values. My assumption is that it is in the list of values and is fairly early in that list.
When you use the True parameter in a VLookUp, the area you are looking up must be sorted in ascending order on the lookup column.
Regards, Dave S
----- Original Message -----
From: "David Lanman" <who1012000@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Saturday, September 17, 2011 12:52 AM
Subject: Re: [ExcelVBA] Vlookup(Date, range, 1, true)
Hello,
I am using all of the parameters, the lookup value is another cell, the
table is fairly large, I am returning the first column (just to make sure it
is working) and I use True, because the date will not always be there, but
the closest is fine. If I use False, it works, unless the date is not there,
then I get #N/A, which is expected. If I use True, it fails, even if the
date is there.
How might I do the Vlookup if I assume that the dates are all Default
Midnight and that the date will not likely be exact even for the day, let
alone the time.
In the case that I am looking at the lookup dates are all 12/31/yyyy, going
back 10 years. The table dates are daily and go about about 10 years.
What is returned is always 1/3/2000.
Thanks,
David
From: Paul Schreiner <schreiner_paul@att.net>
To: ExcelVBA@yahoogroups.com
Sent: Friday, September 16, 2011 7:30 AM
Subject: Re: [ExcelVBA] Vlookup(Date, range, 1, true)
There are several, several and... several... possibilities.
without seening your data and function, it's really difficult to guess.
However... because you said "it always returns the same date".
In your function, are you using all parameters?
the syntax is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
even though they SAY that [range_lookup] is optional, it's really not.
it's only optional if you're willing to accept a value that's "close" to
what
you're looking for.
This value should always be "false"
secondly.. dates are funny things.
Based on the source of the data, even though you may SEE a date, it actually
contains time information.
(try changing the format of the cells and see if it shows you 12:00 as a
time
for all values.
If it does, then the default time is midnight)
This is important, because these two values:
9/16/2011 10:23:14
9/16/2011 10:23:15
will each display as 9/16/2011, but in reality, they're stored as:
40802.4327972222
40802.4328125000
and if you're looking for 9/16/2011, you're really comparing the values of
these
numbers to 40802.00000000
which you won't find, unless one of the values happens to be at midnight.
So... we need more info to move forward with helping you with this one.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: David <who1012000@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Fri, September 16, 2011 10:15:43 AM
Subject: [ExcelVBA] Vlookup(Date, range, 1, true)
Hello All,
I am doing a Vlookup on a date, but it fails. Is there something about dates
that fails? It always returns the same date, which is always incorrect.
Thanks,
David
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1410 / Virus Database: 1520/3899 - Release Date: 09/15/11
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar