+ Reply to Thread
Results 1 to 5 of 5

VLOOKUP date problem

  1. #1
    Registered User
    Join Date
    02-17-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    3

    VLOOKUP date problem

    Hi,

    I'm trying to VLOOKUP a 5/31/2008 date in a table, but I am getting #N/A. I am quite sure same date exists in the table where I am looking.

    Upon evaluating the formula, I noticed that 5/31/2008 is converted by excel into some decimal value (where it should be 39599). The whole number part of 39599 is correct but it is the decimals after this that are causing problems in excel to Vlookup.

    I would appreciate if anyone could help.
    Thanks

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VLOOKUP date problem

    Create a helper column using INT which will return just the whole date.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    02-17-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLOOKUP date problem

    Its works; many thanks for quick reply!

    May I know why it is happening with this particular date?

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VLOOKUP date problem

    Because the 'date' in your lookup table is not just a date but a date and time value. The whole part referring to the date and the decimals the time. Whilst it might be formatted to just look like a date Vlookup requires both the lookup value and the values in the table to be exactly the same if you are checking for an exact match. Fomatting a cell makes no difference to the underlying value hence the #n/a.

    Dom

  5. #5
    Registered User
    Join Date
    02-17-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLOOKUP date problem

    Many thanks for the detail. We initially was able resolve it using ROUND without knowing underlying issue. I think INT would be better approach.

    Many thanks again for great response.
    Appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1