+ Reply to Thread
Results 1 to 6 of 6

how to use lookup a value by date for criteria in Excel sheet

  1. #1
    dom
    Guest

    how to use lookup a value by date for criteria in Excel sheet

    for example

    *table 1

    A B C D
    1 Start End User Rate
    2 ----- ---- ----- -----
    3 1.1.2000 31.12.2000 John $200
    4 1.2.2001 31.12.2001 John $300


    *table 2

    A B C
    1 Date User Rate
    2 ----- ---- -----
    3 3.4.2000 John ??? <--- preferred $200 as table1 "D3"

    I want to get user "John" the Rate by the criteria between Start and End
    from table 1, how can I do.

    thanks

    [email protected]






  2. #2
    Dave R.
    Guest

    Re: how to use lookup a value by date for criteria in Excel sheet

    One way;

    =SUMPRODUCT(($A$3:$A$4<=A9)*($B$3:$B$4>=A9)*($C$3:$C$4=$B9),D3:D4)

    with A9 containing 4/3/2000, B9 containing John, C9 containing the formula
    above.



    "dom" <[email protected]> wrote in message
    news:[email protected]...
    > for example
    >
    > *table 1
    >
    > A B C D
    > 1 Start End User Rate
    > 2 ----- ---- ----- -----
    > 3 1.1.2000 31.12.2000 John $200
    > 4 1.2.2001 31.12.2001 John $300
    >
    >
    > *table 2
    >
    > A B C
    > 1 Date User Rate
    > 2 ----- ---- -----
    > 3 3.4.2000 John ??? <--- preferred $200 as table1 "D3"
    >
    > I want to get user "John" the Rate by the criteria between Start and End
    > from table 1, how can I do.
    >
    > thanks
    >
    > [email protected]
    >
    >
    >
    >
    >




  3. #3
    Max
    Guest

    Re: how to use lookup a value by date for criteria in Excel sheet

    One way ..

    Assuming table 1 is in Sheet1, table 2 is in Sheet2

    In Sheet2
    ----------
    Put in the formula bar for C3:

    =IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(Sheet1!$A$3:$A$100<
    =A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$3:$C$100=B3),0)))

    Array-enter the formula with CTRL+SHIFT+ENTER,
    instead of just pressing ENTER

    Copy C3 down if desired, to return corresponding results
    for other pairs of values in cols A and B

    Adapt the ranges to suit
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    dom <[email protected]> wrote in message
    news:[email protected]...
    > for example
    >
    > *table 1
    >
    > A B C D
    > 1 Start End User Rate
    > 2 ----- ---- ----- -----
    > 3 1.1.2000 31.12.2000 John $200
    > 4 1.2.2001 31.12.2001 John $300
    >
    >
    > *table 2
    >
    > A B C
    > 1 Date User Rate
    > 2 ----- ---- -----
    > 3 3.4.2000 John ??? <--- preferred $200 as table1 "D3"
    >
    > I want to get user "John" the Rate by the criteria between Start and End
    > from table 1, how can I do.
    >
    > thanks
    >
    > [email protected]
    >
    >
    >
    >
    >




  4. #4
    Guest

    Re: how to use lookup a value by date for criteria in Excel sheet

    Thank you for your help, it seem function one of part. I also prefer when I
    change sheet2 "A2" value to 3.2.2001, the sheet2 "C3" value will be changed
    to "$300"

    thanks
    dom


    "Max" <[email protected]> wrote in message
    news:<[email protected]>...
    > One way ..
    >
    > Assuming table 1 is in Sheet1, table 2 is in Sheet2
    >
    > In Sheet2
    > ----------
    > Put in the formula bar for C3:
    >
    > =IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(Sheet1!$A$3:$A$100<
    > =A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$3:$C$100=B3),0)))
    >
    > Array-enter the formula with CTRL+SHIFT+ENTER,
    > instead of just pressing ENTER
    >
    > Copy C3 down if desired, to return corresponding results
    > for other pairs of values in cols A and B
    >
    > Adapt the ranges to suit
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > GMT+8, 1?22' N 103?45' E
    > xdemechanik <at>yahoo<dot>com
    > ----
    > dom <[email protected]> wrote in message
    > news:[email protected]...
    > > for example
    > >
    > > *table 1
    > >
    > > A B C D
    > > 1 Start End User Rate
    > > 2 ----- ---- ----- -----
    > > 3 1.1.2000 31.12.2000 John $200
    > > 4 1.2.2001 31.12.2001 John $300
    > >
    > >
    > > *table 2
    > >
    > > A B C
    > > 1 Date User Rate
    > > 2 ----- ---- -----
    > > 3 3.4.2000 John ??? <--- preferred $200 as table1 "D3"
    > >
    > > I want to get user "John" the Rate by the criteria between Start and End
    > > from table 1, how can I do.
    > >
    > > thanks
    > >
    > > [email protected]
    > >
    > >
    > >
    > >
    > >

    >
    >




  5. #5
    Max
    Guest

    Re: how to use lookup a value by date for criteria in Excel sheet

    Sorry, the formula was insufficiently tested it seems ..
    (notified by the OP in a private email)

    Perhaps better to amend the array formula in Sheet2's C3 to:

    =IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(YEAR(Sheet1!$A$3:$A
    $100)=YEAR(A3))*(Sheet1!$A$3:$A$100<A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$
    3:$C$100=B3),0)))

    Added one more criteria to help ensure unique matching
    to suit the source data format:
    (YEAR(Sheet1!$A$3:$A$100)=YEAR(A3))

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  6. #6
    Max
    Guest

    Re: how to use lookup a value by date for criteria in Excel sheet

    Sorry, the formula was insufficiently tested it seems ..

    Perhaps better to amend the array formula in Sheet2's C3 to:

    =IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(YEAR(Sheet1!$A$3:$A
    $100)=YEAR(A3))*(Sheet1!$A$3:$A$100<A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$
    3:$C$100=B3),0)))

    Added one more criteria to help ensure unique matching
    to suit the source data format:
    (YEAR(Sheet1!$A$3:$A$100)=YEAR(A3))

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    <dom> wrote in message news:[email protected]...
    > Thank you for your help, it seem function one of part. I also prefer when

    I
    > change sheet2 "A2" value to 3.2.2001, the sheet2 "C3" value will be

    changed
    > to "$300"
    >
    > thanks
    > dom




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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