+ Reply to Thread
Results 1 to 6 of 6

Nested If Formula Help Needed

  1. #1
    Registered User
    Join Date
    02-17-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    62

    Question Nested If Formula Help Needed

    Hello:

    I currently am in need of some help to extract the best possible price on a vendor quote. I have attached a spreadsheet.

    What I am looking for is the following:

    1) I have created templates (Seperate) for pricing on SKU's for Vendor A and Vendor B. I have been able to extract by Vlookup my cheapest vendor. This part I have been able to do in Column AC once I paste the SKU in column AB.

    2) I am trying an "HLookup" in Columns AD and AE to pull the vendor "Material Cost" and "Vendor Cost." I am using my cheapest vendor from column AC as my lookup value in the HLookup.

    When I do this for the same SKU I am receiving different costs for Material and Packaging Costs. I am thinking I need a "Nested IF" statement?

    I am extracting the data in Columns AD and AE from master data in columns H,I,K, and L.

    Any help is appreciated.

    Kind Regards.
    Attached Files Attached Files
    Last edited by johnsor1; 07-25-2012 at 10:03 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Nested If Formula Help Needed

    Your F column which you use for your row_index_number in the HLOOKUP formula is different for the 2 rows... is that what is causing your concern? If not, what are you expecting as a result and why?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    02-17-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Nested If Formula Help Needed

    Quote Originally Posted by NBVC View Post
    Your F column which you use for your row_index_number in the HLOOKUP formula is different for the 2 rows... is that what is causing your concern? If not, what are you expecting as a result and why?
    Hi:

    Thanks for taking a looking. I am plugging in a SKU in column AB. Once I do this I am seeking the cheapest vendor to populate in AC and also the cost of the material and packaging in AD and AE.

    In my spreadsheet if I put the same SKU in column AB however I am receiving different material and packaging costs. Somewhere the data I am trying to pull is not extracting the way I am looking for.

  4. #4
    Registered User
    Join Date
    02-17-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Nested If Formula Help Needed

    Bump................

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Nested If Formula Help Needed

    Try in AD3:

    =INDEX($H$3:$I$6,MATCH(AB3,$A$3:$A$6,0),MATCH(AC3,$H$2:$I$2,0))

    and in AE3:

    =INDEX($K$3:$L$6,MATCH(AB3,$A$3:$A$6,0),MATCH(AC3,$K$2:$L$2,0))

    both copied down

  6. #6
    Registered User
    Join Date
    02-17-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Nested If Formula Help Needed

    Quote Originally Posted by NBVC View Post
    Try in AD3:

    =INDEX($H$3:$I$6,MATCH(AB3,$A$3:$A$6,0),MATCH(AC3,$H$2:$I$2,0))

    and in AE3:

    =INDEX($K$3:$L$6,MATCH(AB3,$A$3:$A$6,0),MATCH(AC3,$K$2:$L$2,0))

    both copied down

    It worked! Appreciate the help.

+ 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