+ Reply to Thread
Results 1 to 6 of 6

Index Match to bring back the latest date for a persons contract end (where numerous exist

  1. #1
    Forum Contributor
    Join Date
    07-16-2014
    Location
    Yorkshire
    MS-Off Ver
    MS 10
    Posts
    100

    Index Match to bring back the latest date for a persons contract end (where numerous exist

    Hi there,

    So I have a database of contracts, but some appear more than once, ie they may have more than one contract period, so they would have numerous lines in my spreadsheet with differing start and finish dates.

    I'm doing a bit of a purge on our Purchase orders linked to these contracts and was wanting to pull in the names of the individuals and their latest contract end dates. Issue is mainly down to the fact that the same PO is extended when a new contract period starts, if a new PO was raised I wouldn't have this issue.....whahay to internal process...

    I'm using Index Match, Parameter 0 Exact, but this is bringing back the first occurrence it finds, which for some contracts is incorrect.

    Can anyone help me with a way to add more criteria to bring back the latest date?

    Thanks
    seash

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Index Match to bring back the latest date for a persons contract end (where numerous e

    1 way to do this would be to use LARGE(IF()) combo, but I would need to see some sample data to put that together.

    Another option to consider would be to use filters, filter on name and you will have a list of just that name - you can pick your dates from that (very manual process though)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    07-16-2014
    Location
    Yorkshire
    MS-Off Ver
    MS 10
    Posts
    100

    Re: Index Match to bring back the latest date for a persons contract end (where numerous e

    Sample.xlsx

    Sample file attached, hopefully makes sense

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Index Match to bring back the latest date for a persons contract end (where numerous e

    Try this ARRAY formula...
    =LARGE(IF(Database!$J$5:$J$6=Summary!D4,Database!$O$5:$O$6,0),1)
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  5. #5
    Forum Contributor
    Join Date
    07-16-2014
    Location
    Yorkshire
    MS-Off Ver
    MS 10
    Posts
    100

    Re: Index Match to bring back the latest date for a persons contract end (where numerous e

    Works a treat, thanks

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Index Match to bring back the latest date for a persons contract end (where numerous e

    Happy to help, thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 14
    Last Post: 04-09-2015, 12:43 PM
  2. Replies: 9
    Last Post: 08-27-2014, 03:01 PM
  3. Array Formula Lookup - Bring back Lowest Date + Bring Back Cell Location
    By Matt1998 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2014, 12:08 PM
  4. Replies: 9
    Last Post: 04-30-2013, 03:21 AM
  5. Match two cells and bring back the results from a third
    By amyj22x3 in forum Excel General
    Replies: 3
    Last Post: 10-28-2011, 12:50 PM
  6. Replies: 2
    Last Post: 09-08-2011, 07:08 AM
  7. Look in another sheet match & bring data back
    By gill389 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2011, 01:56 PM

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