Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Match columns A,B in two sheets and Bring ColumC data

    Hello All,

    I have two worksheet having (Match1 ) Sheet and (Match2) sheet . I require match1 sheet column AB with Match2 sheet if matched then bring column C of Match2 sheet to Column C in Match1 sheet. Sample attached.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is this what you want? This won't be correct if there are multiple matches, however.

    =SUMPRODUCT((A3=Match2!$B$2:$B$177)*(B3=Match2!$A$ 2:$A$177)*Match2!$C$2:$C$177)

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Farrukh

    See attached file for one method of returning data based on matching the lookup entries in two columns.
    I combine the two columns into one lookup code on each sheet, in an adjacent spare column.
    Then, using this combined lookup code, return the row number that matches this combined lookup-code.
    If the match isn't found, the formula shows this, otherwise it brings the corresponding value from the desired column.

    Is this what you wanted???

    zeddy
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    An alternate way is to use in C3:
    =IFERROR(AVERAGEIFS(Match2!$C$2:$C$177,Match2!$A$2 :$A$177,B3,Match2!$B$2:$B$177,A3),"")

    and copy it down the column. If more than 1 value it will be averaged, if no values or an error, it will be a null string.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A similar methodolgy to Zeddy's (but without the intermediate columns) is to use the array formula (confirm with ctrl-shift-enter) in C3:
    =IFERROR(INDEX(Match2!$C$2:$C$177,MATCH(B3&";"&A3, Match2!$A$2:$A$177&";"&Match2!$B$2:$B$177,0)),"")

    and copy it down the column. With this formula, if there are more than 1 entry the first entry found will be obtained instead of the average of all of them.

    Steve

Posting Permissions

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