Results 1 to 6 of 6

Thread: Need To Match 2-3 Cells to Show A Result

  1. #1
    New Member JHawks8221's Avatar
    Join Date
    May 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need To Match 2-3 Cells to Show A Result

    Hello, I'm hoping someone might be able to help me please.

    I have a project that I am working on where our IT department needs a report that shows all of our companies and product numbers, but next to each product, they would like us to enter "sales exist" if it shows that the product has sales on the second sheet in the book. The first sheet looks as such with all of our company products listed:
    COMPANY NUMBER PRODUCT NUMBER
    1 497
    25 513
    8 72

    The second sheet has the exact same information, but only contains products that we have existing sales. They would like the first page to show "Sales Exists" in the third column, if both the Company Number and Product Number is listed on the second sheet (due to there being the same products existing between multiple companies and some do now have sales):

    COMPANY NUMBER PRODUCT NUMBER
    1 497 Sales Exists
    25 513
    8 72 Sales Exists

    I was trying VLookup, but couldn't get the formulat to work right, right now, I'm using this formula:

    =IF(ISNA(MATCH(B2,WithSales!B$2:$B$333,0)),"", "Sales Exist")

    which does return the results I'm looking for... however because we have multiple companies that use product number 497 it states that sales exist across all companies, even though the second sheet states only company 01 shows sales for this product.

    I know this is probably an easy fix. I've searched many pages and videos but I was unable to find anything that I could get to work. Would anyone be able to help me with this please? Thank-you very much!

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need To Match 2-3 Cells to Show A Result

    Here's one way.
    This is an array formula and must be entered with CTRL-SHIFT-ENTER.

    Sheet2

    ABC
    1COMPANY NUMBERPRODUCT NUMBER
    21497Sales Exists
    325513Sales Exists
    425497
    52572
    6872Sales Exists
    78497Sales Exists

    Spreadsheet Formulas
    CellFormula
    C2{=IF(ISNA(MATCH(A2&"/"&B2,Sheet1!$A$2:$A$5&"/"&Sheet1!$B$2:$B$5,0)),"","Sales Exists")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Sheet1

    AB
    1COMPANY NUMBERPRODUCT NUMBER
    21497
    325513
    4872
    58497


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    New Member JHawks8221's Avatar
    Join Date
    May 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need To Match 2-3 Cells to Show A Result

    This worked perfectly! I can't thank-you enough for helping with this Thank-you again so much!

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Need To Match 2-3 Cells to Show A Result

    Here is another way with a normal-entry formula

    Sheet2

    ABC
    1COMPANY NUMBERPRODUCT NUMBER
    21497Sales exist
    325513Sales exist
    425497
    52572
    6872Sales exist
    78497Sales exist

    Spreadsheet Formulas
    CellFormula
    C2=IF(COUNTIFS(Sheet1!A$2:A$10,A2,Sheet1!B$2:B$10,B2),"Sales exist","")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member JHawks8221's Avatar
    Join Date
    May 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need To Match 2-3 Cells to Show A Result

    Just tried this today as well and it worked also. Thank-you so much for the help! I really appreciate it

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Need To Match 2-3 Cells to Show A Result

    Quote Originally Posted by JHawks8221 View Post
    Just tried this today as well and it worked also. Thank-you so much for the help! I really appreciate it
    You're very welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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