Lookup & Match From 2 Date Based Tables

  • Hi,
    I need to match 2 tables and return the quote price with the begin date & end date, which the
    1st table consists of all orders records & the 2nd table consists of quote/price contract (based on the effectiveness). i need to match based on the part#(table1&table2), then compare the order date (from table1) with the quote begin & end date (from table2) and return the quote price(from table2).

    Part# Order# OrderDate(dd/mm/yyyy)
    xyz 101 02/01/2007
    abc 102 15/04/2007
    edf 103 06/07/2007
    xyz 104 01/12/2007
    edf 105 01/12/2007

    Part# BeginDate EndDate Price
    abc 01/01/2007 15/02/2007 1.30
    abc 16/02/2007 31/01/2008 2.30
    edf 01/01/1997 31/12/2099 2.00
    xyz 15/01/2004 31/12/2010 5.66
    edf 31/03/2007 31/12/2099 4.33

    Appreciate for your feedback.

  • Re: Lookup & Match From 2 Tables

    The two entries in table 2 for edf have dates which overlap (after 31/3/2007); which to take? The one with the later start date would probably be the one you want. In which case, why have an end date at all? Just assume that the right price is the one for the latest start date before the order date. This is what I've assumed in the attached.
    The formula in D3 was array-entered (holding the Ctrl and Shift keys down while pressing the Enter key) and copied down to D7.

  • Re: Lookup & Match From 2 Date Based Tables

    Thanks, it was a great help! Wonderful![hr]*[/hr] Auto Merged Post;[dl]*[/dl]Thanks a bunch! Its works!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!