computing
  • 0

Vlookup date problems

  • 0

I have really struggled to find a solution on the net but to no avial.
I have a date column [spreadsheet B]with the date linked to another spreadsheet A. The date format in both are dd/mm/yyy.
In the spreadsheet B i have two column column S with for excample 01-Jan, 01-Feb, 01-Mar, 01-Apr and next to each a text value I want to lookup using the date column of the spreadsheet B
I have tried al the problem solving answers on the Net but always get a #N/A or #value!
Please help

Share

1 Answer

  1. Hi,
    One of the problems with looking up and matching dates is that Excel stores times in the same variable as the date.

    If some of your date values also contain times, there will not be a match.

    Two cells formatted as dd/mm/yy may show 10/Jan/10
    but one of them is midnight and the other is noon – you can’t tell by looking at the date formatted cell.
    10/Jan/2010 at midnight is 40198.00
    10/Jan/2010 at noon is 40198.50

    Try formating your dates as numbers and see if there are any decimal values ‘hidden’ in the cells.

    If the values in the cells are not around 40000 (assuming recent dates), but still show as a date, then Excel hasn’t recognized the cell as a date. So some cells are text that is in a date style, while others are numbers recognized by Excel as dates and showing as a date due to formatting. So again – no match.

    One way to handle the time issue is to compare the integer parts of the numbers only.

    Create a new column to the right of your dates, before the text and use a formula =int(A1), to ensure that you get only the integer (whole number) part. Do your lookup on this new column using =VLOOKUP(INT(A9),B!$A$2:$B$20,2,FALSE), so you compare the integer part of the date in cell A9 with the integer values in your new column.

    Regards

    • 0