VLOOKUP question

[Jeff Norville]

It's Monday so I suspect this is a personal quirk rather than one in
Excel, but for the life of me it looks like a programmatic quirk.

So I'm looking through a list of timestamps (ws = LOOKUPDATA) for
matching date/times, then taking the associated reading (from the
range REFDATA!A2:B3457); out of a test range of 50 values, nine return
"#N/A" which means the formula couldn't find a match. However, I see
those matches when I do my own Find.

Here's the formula:
=VLOOKUP(LOOKUPDATA!D2, REFDATA!A2:B3457,2, FALSE)

The data referenced in
5/22/09 10:00 <--VLOOKUP finds a match fine
5/22/09 11:00 <--VLOOKUP finds no match

5/22/09 11:00 pasted as string is:
39955.4583333333

The other erring dates, when pasted as string, also end in the "3-bar"
pattern; weird. However, using the Excel "Find" I can find the cells
fine.

Is Excel not matching two date/time columns even though they're
formatted identically?

More importantly -- fix suggestions?

Regards,
Jeff

 

[ryguy7272]

I think dates should be dates, not strings, and that's why you're
encountering a rounding issues, right. Try something like this:
=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Also, you may want to look in the Help area. I just found this there:
If range_lookup is TRUE, the values in the first column of table_array must
be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.

Finally, you may consider an Index/Match function:
http://www.contextures.com/xlFunctions03.html

I prefer that to Vlookup.

HTH,
Ryan---