+ Reply to Thread
Results 1 to 4 of 4

match closest date from within range matching ID number

  1. #1
    Registered User
    Join Date
    12-08-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    2

    match closest date from within range matching ID number

    Hi all,

    I've been struggling with this one for a while:
    In sheet 1 I have a number of observations on different people identified by an ID number in column A ("ID"). Each observation has a date in column B ("Date1") and every person may appear multiple times. In sheet 2, I have more observations on the same people with ID numbers in column A ("ID") and different dates in column B ("Date2") referring to different events than those in sheet 1. Again, every person may appear multiple times.
    Now, what I need to do is search for the dates in sheet2 that are the closest before and after each date in sheet1 for a particular ID number. I named these new columns "ClosestDate2Before" and "ClosestDate2After".
    I managed to match the dates closest before and after from the whole range of dates in sheet2 with this formula (as currently shown in cells C2 and D2):
    "ClosestDate2Before" =INDEX(Sheet2!B:B,MATCH(Sheet1!B2,Sheet2!B:B,1))
    "ClosestDate2After" =INDEX(Sheet2!B:B,MATCH(Sheet1!B2,Sheet2!B:B,-1))
    Incidentally, the closest date after formula does not seem to work (it returns #N/A! not just for the first cell but every cell of the file) and I'd very much appreciate help on this (the dates may have to be sorted a differently?).
    But the real problem is that I need to search for matching dates only within those rows of sheet 2 that contain the same ID number as the row in sheet 1, so the look-up range has to be restricted to those rows in sheet 2 with the same ID as the row in sheet 1.

    Any help is greatly appreciated! Thanks so much!
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: match closest date from within range matching ID number

    Hello and welcome to the forum.

    For closest date before, use below formula:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and for closest date after, use below formula:-

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached: match ID, date.xlsx

    regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    12-08-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: match closest date from within range matching ID number

    धन्यवाद, dilipandey!

    That worked perfectly

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: match closest date from within range matching ID number

    You are welcome..

    Suggest you to mark this thread as [SOLVED].. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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