+ Reply to Thread
Results 1 to 7 of 7

Getting Part Number Based on Two Criteria

  1. #1
    Registered User
    Join Date
    01-22-2019
    Location
    CT, USA
    MS-Off Ver
    365
    Posts
    3

    Getting Part Number Based on Two Criteria

    Hello all. Forgive me for the vague title. I'm a casual excel user so my nomenclature may be a bit off.

    I need a cell to pull a part number based on the results of A8 and C8

    A8 is a dropdown (has 3 results).
    If A8 is = to A18, then there is no need to proceed.
    If A8 is = to A19 or A20, then the result of C8 needs to be taken into account.
    C8 is a "roundup" based on an "if" (which includes an Index Match) of B8. This works great.

    Together, the results of A8 and C8 (depending on what combo that is) would use Vlookup to get a result (the P/N) for D8

    This is for spec'n a transformer for one of our systems.
    A8 is the primary power (240V 1ph, 480V 3ph, or 600V 3ph)
    B8 is the systems actual kVA demand + 20% [this is taken from another chart which has tons of variables for all the possible power draws]
    C8 is a roundup to an actual kVA transformer number (15,30,45,75,etc)
    [All of this works, so far]
    D8 needs to put the incoming power (A8 result) with the correct kVA transformer (C8 result) to pull the appropriate P/N
    If A8 = 240V 1ph, then there is no need for a xfmr.

    This will then be referenced in an earlier sheet, to input a $quote, for the project.

    My workflow was thinking something along the lines of:

    if A8=A18, [true] do nothing, [false] then follow below
    if A8=A19 & C8=(C19 or C20 or C21 or C22) , [true] VLOOKUP(C8,C19:D22,2,False), [false] then follow below
    if A8=A20 & C8=(C23 or C24 or C25 or C26) , [true] VLOOKUP(C8,C23:D26,2,False), [false] there should be an error


    I'm aware that I may be using the incorrect formulas.

    Thank you,
    -T
    Last edited by AliGW; 01-22-2019 at 04:55 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,072

    Re: Getting Part Number Based on a Two Criteria

    Welcome to the forum!

    I have changed it for you this time, but you knew already that your thread title was unsuitable. Please note the change and ensure that you provide better thread titles in future. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,478

    Re: Getting Part Number Based on Two Criteria

    If you attach a sample workbook, it would help.
    Try following, see if it helps.

    =if(A8=A18, "", if(A8=A19,iferror(vlookup(C8,C19:D22,2,False),if(A8=A20,Vlookup(C8,C23:D26,2,False),""))))

    Hopefully, syntax is correct.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  4. #4
    Registered User
    Join Date
    01-22-2019
    Location
    CT, USA
    MS-Off Ver
    365
    Posts
    3

    Re: Getting Part Number Based on Two Criteria

    I forgot to upload the file to my onedrive, but I'll give these a try in the morning. Thanks

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,072

    Re: Getting Part Number Based on Two Criteria

    No, we don't want a OneDrive link - there is no need. We have an attachment facility here. In any case, as a new member, you will not be able to post a link.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  6. #6
    Registered User
    Join Date
    01-22-2019
    Location
    CT, USA
    MS-Off Ver
    365
    Posts
    3

    Re: Getting Part Number Based on Two Criteria

    modytrane,
    Thank you for your assistance. Your formula gets me close. All is ok w/ the 240 and 480 bits, but inputting the 600 value gives my cell a result of "FALSE". I'll give it a few goes at some modifications.

    AliGW,
    Respectfully, you need to relax. I appreciate you modifying my thread title, but the warning was quite passive-aggressive. The title was, semantically, related to the query and fitting w/ the forum rules. Also, nowhere did I mention linking or supplying a OneNote file. I merely stated I forgot to upload one, without going into further detail. The necessity being my ability to access the file, from my home PC, and giving modytrane's suggestion a try. I've been using online forums for over 3 decades and can count my interactions w/ mods on a single hand. However, not 12hrs on this site and here you are, twice. I'm sure your a good person and love to help others, but learn to recognize your boundaries. If this is foresight into the type of experience the forum has to offer, thank you, but I'll seek assistance somewhere else.

    ETA: The working formula is =IF(A8=A18,"",IF(A8=A19,VLOOKUP(C8,C19:D22,2,FALSE),IF(A8=A20,VLOOKUP(C8,C23:D26,2,FALSE),ERROR)))
    Last edited by Stuck at Work; 01-23-2019 at 09:26 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,072

    Re: Getting Part Number Based on Two Criteria

    With respect to you, I have no knowledge of your Internet history, only that you are a new member here and clearly need guidance regarding the way that THIS forum works. The warning was polite and to the point: there was nothing actively or passively aggressive about it. Please do not make presumptions about me or my boundaries, or make similar comments about anybody else here. 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