+ Reply to Thread
Results 1 to 6 of 6

Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

  1. #1
    Registered User
    Join Date
    08-18-2010
    Location
    North Alabam
    MS-Off Ver
    Excel 2003
    Posts
    8

    Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

    I have a workbook I'm working on. I need to look at the values in Sheet 1 cells A2 & B2, then look at sheet #2 find the same two values which could be in A3:A25 and C3:C25. When I find a match return the value in that matching row from sheet 2 cell E* (same row match was found).

    Simpler way I guess to explain it If the Plant Code & Part Number match on Sheet 1, Find the same Row with the matching Plant Code and Part Number and Tell me the skid qty.

    Sheet 1
    SHEET1.jpg

    Sheet 2
    SHEET2.jpg

    I've tried some Vlookup and Match but I can't seem to get anything to work. I'll take VBA or just a cell formula whatever will work.

    I just filled in the values on sheet # 1 so you could see what I want the results to be! thanks
    Last edited by billfloyd806; 02-21-2013 at 01:13 PM.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

    Try this array fomrula
    Please Login or Register  to view this content.
    Confirm with Ctrl+Shift+Enter and not just Enter

    or this normal formula
    Please Login or Register  to view this content.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

    Here is another approach:

    =SUMPRODUCT(($A$3:$A$60=A3)*($B$3:$B$60=B3)*(Sheet2!$A$3:$A$60=Sheet1!A3)*(Sheet2!$C$3:$C$60=Sheet1!B3),Sheet2!$E$3:$E$60)
    Last edited by newdoverman; 02-21-2013 at 06:22 PM.

  4. #4
    Registered User
    Join Date
    08-18-2010
    Location
    North Alabam
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

    Sweet!!!! Works Perfect. Now, if a cell doesnt have a value it displays #N/A. How do I make if a cell doesnt have any value its just blank>?

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

    I can't reproduce your error, probably due to not having your exact data but try this:

    =IFERROR(SUMPRODUCT(($A$3:$A$60=A3)*($B$3:$B$60=B3)*(Sheet2!$A$3:$A$60=Sheet1!A3)*(Sheet2!$C$3:$C$60=Sheet1!B3),Sheet2!$E$3:$E$60),"")

  6. #6
    Registered User
    Join Date
    08-18-2010
    Location
    North Alabam
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Match 2 cell values on Sheet 1 and find two cells with same matching value on sheet 2

    Perfect! Thanks!

+ 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