Hi Bob,


I have attached a sample of what I am trying to do!!!

Attached Files

microsoft lookup date test.zip (80.7 KB, 12 views)

I see the problem. You're searching for a particular date in a column that has dates and times; there's no exact match.

Here's why: The timestamp "2014-01-15 22:39:23" is stored in Excel as 41654.9440162037. That's 41654 days from 1900-01-01 — which gets you to the end of 2014-01-14 — plus .9440162037 of another day, which gets you to 22:39:23 of the next day. Never mind that it looks like a year, month, day and time; the format code tells Excel to display it that way, but the internal value is one single large number.

So here you come along and ask the MATCH function to find 2014-01-15. But what Excel sees is 41654—that is, 41654.000—and when it tries to find 41654 in Master-date!B:B, it doesn't see anything that matches than value exactly.

So what do you do about it? Well, here's one way: I created a new column in in Master-date!I:I with formula =INT(B2). This takes the date from column B and throws away the fractional part; what's left is not a date and time, but just the date part. Then I changed the formula in 'Lookup by Date'!H:H to point to column I in Master-date instead of column B, like this:...Oops; I use R1C1 notation by habit. I mean it looks like this:I didn't check for any other errors, but the table all filled in, when I did that, with valid-looking data.

One thing: In column H of 'Lookup by Date', when I made that change, Excel displayed that column of row numbers as dates, eg "5-22". That means row 142, for the reason I explained above. The lookup and the rest of the table works fine, it just looks strange. All you have to do is changed the format of that column to General and it returns to normal.