+ Reply to Thread
Results 1 to 5 of 5

find a value at an intersection?

  1. #1
    Ellen G.
    Guest

    find a value at an intersection?

    I have the following data:

    I need to lookup the part number and trancode in Worksheet2 and return the
    quantity to worksheet1.

    Worksheet1
    A B C D E F G H I J
    TraneCodes 2 6 8 12 28 36 54
    60........................
    1 PART NUMBER
    2 003-0623-00
    3 005-0030-00
    4 005-0168-00
    5 005-0320-00
    6 005-0420-00
    7 005-0539-00
    8 005-0798-01
    9 005-7013-00


    Worksheet2:

    A B C D
    1 Part# TranCode RecvPlt Qty
    2 003-0018-01 28 7 10
    3 003-0018-01 40 7 10
    4 003-0021-00 8 54 25
    5 003-0021-04 4 63
    6 003-0021-04 6 11 66
    7 003-0021-04 11 1
    8 003-0059-00 4 649
    9 003-0059-00 22 22



  2. #2
    Bob Phillips
    Guest

    Re: find a value at an intersection?

    =IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B$2:$B$9),0)),""
    ,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B
    $2:$B$9),0)))

    which is an array formula, it should be committed with Ctrl-Shift-Enter, not
    just Enter.

    copy down and across, but I get no matches with your data.

    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Ellen G." <[email protected]> wrote in message
    news:[email protected]...
    > I have the following data:
    >
    > I need to lookup the part number and trancode in Worksheet2 and return the
    > quantity to worksheet1.
    >
    > Worksheet1
    > A B C D E F G H I

    J
    > TraneCodes 2 6 8 12 28 36 54
    > 60........................
    > 1 PART NUMBER
    > 2 003-0623-00
    > 3 005-0030-00
    > 4 005-0168-00
    > 5 005-0320-00
    > 6 005-0420-00
    > 7 005-0539-00
    > 8 005-0798-01
    > 9 005-7013-00
    >
    >
    > Worksheet2:
    >
    > A B C D
    > 1 Part# TranCode RecvPlt Qty
    > 2 003-0018-01 28 7 10
    > 3 003-0018-01 40 7 10
    > 4 003-0021-00 8 54 25
    > 5 003-0021-04 4 63
    > 6 003-0021-04 6 11 66
    > 7 003-0021-04 11 1
    > 8 003-0059-00 4 649
    > 9 003-0059-00 22 22
    >
    >




  3. #3
    Ellen G.
    Guest

    Re: find a value at an intersection?

    Thank Bob - This doesn't reference my quantity column though?
    I need it to return the quantity of parts when both conditions agree.

    "Bob Phillips" wrote:

    > =IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B$2:$B$9),0)),""
    > ,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B
    > $2:$B$9),0)))
    >
    > which is an array formula, it should be committed with Ctrl-Shift-Enter, not
    > just Enter.
    >
    > copy down and across, but I get no matches with your data.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Ellen G." <[email protected]> wrote in message
    > news:[email protected]...
    > > I have the following data:
    > >
    > > I need to lookup the part number and trancode in Worksheet2 and return the
    > > quantity to worksheet1.
    > >
    > > Worksheet1
    > > A B C D E F G H I

    > J
    > > TraneCodes 2 6 8 12 28 36 54
    > > 60........................
    > > 1 PART NUMBER
    > > 2 003-0623-00
    > > 3 005-0030-00
    > > 4 005-0168-00
    > > 5 005-0320-00
    > > 6 005-0420-00
    > > 7 005-0539-00
    > > 8 005-0798-01
    > > 9 005-7013-00
    > >
    > >
    > > Worksheet2:
    > >
    > > A B C D
    > > 1 Part# TranCode RecvPlt Qty
    > > 2 003-0018-01 28 7 10
    > > 3 003-0018-01 40 7 10
    > > 4 003-0021-00 8 54 25
    > > 5 003-0021-04 4 63
    > > 6 003-0021-04 6 11 66
    > > 7 003-0021-04 11 1
    > > 8 003-0059-00 4 649
    > > 9 003-0059-00 22 22
    > >
    > >

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: find a value at an intersection?

    My mistake

    =IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B$2:$B$9),0)),""
    ,INDEX(Sheet2!$D$1:$D$9,MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B
    $2:$B$9),0)))


    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Ellen G." <[email protected]> wrote in message
    news:[email protected]...
    > Thank Bob - This doesn't reference my quantity column though?
    > I need it to return the quantity of parts when both conditions agree.
    >
    > "Bob Phillips" wrote:
    >
    > >

    =IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B$2:$B$9),0)),""
    > >

    ,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$1=Sheet2!$B
    > > $2:$B$9),0)))
    > >
    > > which is an array formula, it should be committed with Ctrl-Shift-Enter,

    not
    > > just Enter.
    > >
    > > copy down and across, but I get no matches with your data.
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (replace somewhere in email address with gmail if mailing direct)
    > >
    > > "Ellen G." <[email protected]> wrote in message
    > > news:[email protected]...
    > > > I have the following data:
    > > >
    > > > I need to lookup the part number and trancode in Worksheet2 and return

    the
    > > > quantity to worksheet1.
    > > >
    > > > Worksheet1
    > > > A B C D E F G H I

    > > J
    > > > TraneCodes 2 6 8 12 28 36 54
    > > > 60........................
    > > > 1 PART NUMBER
    > > > 2 003-0623-00
    > > > 3 005-0030-00
    > > > 4 005-0168-00
    > > > 5 005-0320-00
    > > > 6 005-0420-00
    > > > 7 005-0539-00
    > > > 8 005-0798-01
    > > > 9 005-7013-00
    > > >
    > > >
    > > > Worksheet2:
    > > >
    > > > A B C D
    > > > 1 Part# TranCode RecvPlt Qty
    > > > 2 003-0018-01 28 7 10
    > > > 3 003-0018-01 40 7 10
    > > > 4 003-0021-00 8 54 25
    > > > 5 003-0021-04 4 63
    > > > 6 003-0021-04 6 11 66
    > > > 7 003-0021-04 11 1
    > > > 8 003-0059-00 4 649
    > > > 9 003-0059-00 22 22
    > > >
    > > >

    > >
    > >
    > >




  5. #5
    Beat
    Guest

    RE: find a value at an intersection?

    Hi Ellen,

    I would try hlookup() to search for "TraneCodes",
    (add a extra Row below TraneCodes to indicate the row number);
    use vlookup() to search for the Partnumber and use the "row-result" from
    hlookup() as ofset ...

    Clear enough?

    "Ellen G." wrote:

    > I have the following data:
    >
    > I need to lookup the part number and trancode in Worksheet2 and return the
    > quantity to worksheet1.
    >
    > Worksheet1
    > A B C D E F G H I J
    > TraneCodes 2 6 8 12 28 36 54
    > 60........................
    > 1 PART NUMBER
    > 2 003-0623-00
    > 3 005-0030-00
    > 4 005-0168-00
    > 5 005-0320-00
    > 6 005-0420-00
    > 7 005-0539-00
    > 8 005-0798-01
    > 9 005-7013-00
    >
    >
    > Worksheet2:
    >
    > A B C D
    > 1 Part# TranCode RecvPlt Qty
    > 2 003-0018-01 28 7 10
    > 3 003-0018-01 40 7 10
    > 4 003-0021-00 8 54 25
    > 5 003-0021-04 4 63
    > 6 003-0021-04 6 11 66
    > 7 003-0021-04 11 1
    > 8 003-0059-00 4 649
    > 9 003-0059-00 22 22
    >
    >


+ 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