Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP question
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 the 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jeff Norville" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup question | Excel Discussion (Misc queries) | |||
VBA question - vlookup | Excel Programming | |||
=vlookup question | Excel Discussion (Misc queries) | |||
vlookup vba question. | Excel Discussion (Misc queries) | |||
vlookup question | Excel Programming |