Results 1 to 5 of 5

Thread: Need to match 2 criteria with 2 criteria to pick up a value

  1. #1
    Seeker ctiger's Avatar
    Join Date
    Nov 2013
    Posts
    16
    Articles
    0
    Excel Version
    2013

    Need to match 2 criteria with 2 criteria to pick up a value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All, I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?
    Item lot Amt Items Lot #s Yds
    10429.50.0 N49950 2.5 0201.019FLAME.0 T37530 9
    10651.1.0 2 10076.9.0 937198-01 2.5
    1100MG.MULTI STRIPE.0 57 10337.1.0 973924-01 2.75
    1100MG.MULTI STRIPE.0 5.375 10337.101.0 I08572 7.75
    11074.4.0 1 10337.101.0 973926-01 6.75
    11820.10.0 2.25 10337.330.0 B87337 5
    10337.101.0 I08572 7.75 10337.6.0 973923-01 2.375
    11820.101.0 5 10370.515.0 938084 7
    11820.111.0 17.625 10397.16.0 937622-01 3.25
    11820.1111.0 5.625 10397.16.0 937622-02 3
    11820.1212.0 9.625 10397.3.0 937621-01 6
    11820.1616.0 4.5 10397.9.0 748647 8
    11836.9.0 32.5 10422.4.0 961921-01 2
    11898.11.0 1.625 10429.50.0 K72557 38.875
    11898.4.0 2.625 10429.50.0 N49950 2.5
    11898.505.0 2.5 10429.9.0 W45328 8.75

  2. #2
    Seeker ctiger's Avatar
    Join Date
    Nov 2013
    Posts
    16
    Articles
    0
    Excel Version
    2013
    My mistake on the original description :drop the Lot # form column B into column E if there is a match? Should be the other way around.

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Try this array formula:

    =INDEX($E$3:$E$19,MATCH($A4&$C4,$D$3:$D$19&$F$3:$F$19,0))
    Ive used your layout above, with the
    addition of one row at the bottom to provide a record that matches with your second data row.
    ***IMPORTANT***
    with this type of formula you have to code it to suit a particular cell, so that when its copied to other cells, Excel makes the correct adjyustments for the new position. This one is coded for B4, because is coded to pick up A4 and C4, so this is where you need to put it first. If you are forced to start on another row (eg B6, then amend A4, C4 to A6 , C6.
    Next you need to adjust the groups of range addresses to suit the real size of your table of data.
    Then click the cell with your adjusted version of the formula in. Press F2 to place the formula in Edit mode. Press CONTROL+SHIFT+ENTER
    If alls well you will see (in the formula bar that Excel has placed brace characters{} at either end of the formula.
    This is the only way to do this, and indicates that the formula has array status. If you amend the formula at any time, this process must be repeated.
    When the braces are in place, you can copy the formula with its braces to other cells in the column where you need a search result. If you want to override no match errors you can add IFERROR or IF(ISERROR( to the formula.
    Attached Files Attached Files

  4. #4
    Seeker ctiger's Avatar
    Join Date
    Nov 2013
    Posts
    16
    Articles
    0
    Excel Version
    2013
    Works great, will save me a great deal of time. Thanks so Much.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by ctiger View Post
    Works great, will save me a great deal of time. Thanks so Much.
    Glad to be of help. I just noticed that in the example I left the formula in B4 in text form, so sorry about that
    Obviously you figured it out though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •