Answered by:
VBA Vlookup won't match dates
Question

Hi all,
First post here and I'm hoping someone can help me with a problem I'm having with Vlookup and dates. My knowledge of VBA is quite limited, so I'm hoping this is a quick fix.
So, I have a spreadsheet with 2 named ranges "Source" and "Compare", which are one and two columns wide. I have dates in the first range, which I'm trying to match to the dates in the second range. I have written a short VBA Vlookup code to compare these dates in an array (the spreadsheet has 18,000 rows of data so I thought using arrays would be the quickest way to do the vlookup), but it won't match any dates. I've tested the code using numbers and text instead of dates, but when I put the dates back in it just won't work. The code should then paste the array into another named range on my spreadsheet ("Match").
I've tried everything I can think of  checking date formats are the same, checking for any spaces, converting the dates to a number to see if they are the same, using a true / false check in the spreadsheet to see if the cells are the same.
The dates in the first range were originally formatted as "custom" but I changed these to date before running the macro. My VBA code is as follows:
Sub lookup()
Dim MyArray()
x = Range("Source").Value
y = Range("Compare").Value
RowNumbers = 18000
ReDim MyArray(1 To RowNumbers)
For r = 1 To RowNumbers
MyArray(r) = Application.VLookup(x(r, 1), y, 2, False)
Next r
Range("Match").Value = Application.WorksheetFunction.Transpose(MyArray)
End SubAny help would be greatly appreciated, thanks.
Answers

Possibly because the Excel worksheet function gets confused with the array of VBA dates which are always UScentric.
As suggested convert the dates to intrinsic values which you can do simply with the Value2 property, eg
x = Range("Source").Value2
Alternatively pass the actual ranges containing the values
Dim x as Range
Set x = Range("Source")Looks like you are dealing with a large ranges, experiment both ways. With a large set it might be quicker to avoid the worksheet function and roll your own, particularly if sorted in which case search and look into searching the array with a "binary chop"
In passing, no need to do that Transpose, write to a 2D arrray
ReDim MyArray(1 To RowNumbers, 1 To 1)
MyArray(r, 1) = xxx
and finally simply
Range("Match").Value = MyArray Marked as answer by Hans Wilsdorf Saturday, October 4, 2014 12:19 AM
All replies

Re: no match on dates w Vlookup
Try pasting both arrays on another sheet and take a quick look at them.
Is the entry in the formula bar identical for "identical" items?
You might want to change both ranges to numbers, do the match and then convert the result back to dates.
'
Jim Cone(free & commercial excel addins & workbooks)
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox) Edited by James Cone Monday, October 31, 2016 7:01 PM


Re: dates to numbers
You should be able to change the the entire range cell from Dates to Numbers using the normal cell formatting utility.
I would make sure to use just enough decimal places to ensure the comparison you require.
If you want to find "04/01/2014" in both lists, then you want whole numbers.
If "04/01/2014 12:48:40 PM" is essential to the comparison then you will need sufficient decimal places.
'
Jim Cone 

Thank you for your kind help, and also to Jim. Converting dates to numbers works, but I still don't understand why the vlookup won't work using the actual dates. I'm sure it must be something to do with the formatting of the data but I just can't see what. I have created a dummy worksheet but can't see how I can upload this.

Possibly because the Excel worksheet function gets confused with the array of VBA dates which are always UScentric.
As suggested convert the dates to intrinsic values which you can do simply with the Value2 property, eg
x = Range("Source").Value2
Alternatively pass the actual ranges containing the values
Dim x as Range
Set x = Range("Source")Looks like you are dealing with a large ranges, experiment both ways. With a large set it might be quicker to avoid the worksheet function and roll your own, particularly if sorted in which case search and look into searching the array with a "binary chop"
In passing, no need to do that Transpose, write to a 2D arrray
ReDim MyArray(1 To RowNumbers, 1 To 1)
MyArray(r, 1) = xxx
and finally simply
Range("Match").Value = MyArray Marked as answer by Hans Wilsdorf Saturday, October 4, 2014 12:19 AM

Thank you so much, this has been a massive help. Both solutions you suggested have worked, but using the set x = range solution has sped my macro up enormously (I've used this in other parts of my code). My macro now runs in less than 2 minutes compared to 8 previously. Thanks again, I really am very grateful.

Glad things have improved but 2 minutes seems a long time. If say you have a lookup table of 18000 rows, and looping 18000 lookups, something under a couple of seconds should be possible, even if that means sorting the table elsewhere and rolling your own lookup function.
 Edited by Peter Thornton (Excel MVP 200813)Moderator Saturday, October 4, 2014 3:38 PM