+ Reply to Thread
Results 1 to 8 of 8

Formula help to choose lowest cost from multiple vendors and then choose vendor

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Formula help to choose lowest cost from multiple vendors and then choose vendor

    Hello everyone,

    I need help with figuring out a formula for the attached sheet. Our company has multiple vendors for the same part numbers. In the attached sheet, I laid out an example of how our master item list is represented. What we are trying to do is pull the lowest cost into the "Lowest Cost" cell which is pulling the data from Vendor 1 thru Vendor 18. Our difficulty comes in for the "Preferred Vendor" column. We would like a formula that takes that "Lowest Cost" and pulls the vendors name associated with that lowest cost. I used a Small formula for lowest cost and an Index formula for the Preferred Vendor. The attached sheet has the formula built in but I included them below.

    For lowest cost: =SMALL(D2:U2,1)
    For preferred vendor: =INDEX($D$1:$U$1,SUMPRODUCT((D2:U2=C2)*COLUMN(D2:U2))-COLUMN(C2:C2))

    The problem is that the formula for Preferred Vendor sometimes picks up either the incorrect vendor, a vendor that shows no pricing, and occasionally the correct vendor. Also, some of our vendors may have exactly the same lowest cost. In that case, we are fine if the preferred vendor column shows #REF or an error.

    Any help you can provide would be much appreciated. Thank you!

    test.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,243

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    Hi and welcome to the forum

    Instead of...
    =INDEX($D$1:$U$1,SUMPRODUCT((D2:U2=C2)*COLUMN(D2:U2))-COLUMN(C2:C2))
    Try thius...
    =INDEX($D$1:U2,1,MATCH(C2,D2:U2,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-26-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    Thank you! Glad to be here and appreciate the help!

    The only issue that results from the new formula is that when there are two vendors with the same lowest cost, this formula chooses the left most lowest cost vendor. In these situations, we would like it to display an error message so that we can then manually choose the preferred vendor. Is this possible?

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,981

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    Please Login or Register  to view this content.
    ;
    vendorList:= E1:V1
    Attached Files Attached Files
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    07-26-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    Thanks again! This is working almost exactly like we would want!

    The only issue is the following, which is what we have been running into as well... As you can see on your sheet, on Row 2, 4 and 5, your formula has chosen Vendor 3. However, on those same rows, there are two vendors with the same "lowest cost" of $25.30. In these type of situations, is there a way that the formula can produce an error or produce a statement saying "Choose Vendor" for example, instead of automatically picking the left most lowest cost vendor?

    The reasoning is that in these situations, our purchasing department can filter out these errors and manually choose the preferred vendors and then import that data into our system. However, if the formula automatically chooses between two vendors with the same lowest cost, like in the situations on Row 2, 4, and 5, then our purchasing department could miss evaluating significant data between the two vendors before making their final decision.

    Thank you for your continued help and have a wonderful weekend!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,243

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    Try this instead. You can of course change the text lol...

    =IF(COUNTIF(D2:U2,C2)>1,"ERROR/CHOOSE!!",INDEX($D$1:U2,1,MATCH(C2,D2:U2,0)))

  7. #7
    Registered User
    Join Date
    07-26-2013
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    You guys are amazing! This is perfect and works exactly how we need it to. Thank you very much!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,243

    Re: Formula help to choose lowest cost from multiple vendors and then choose vendor

    happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-09-2012, 12:28 AM
  2. Lowest Cost Vendor
    By rafilip in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2012, 01:59 PM
  3. Lowest Cost Vendor
    By rafilip in forum Excel General
    Replies: 0
    Last Post: 06-25-2012, 01:31 PM
  4. Find a combination and choose the lowest one
    By johan314 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2009, 07:59 AM
  5. Finding Lowest Cost/Vendor
    By nadiaz in forum Excel General
    Replies: 2
    Last Post: 10-24-2007, 11:20 AM

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