+ Reply to Thread
Results 1 to 6 of 6

Creating an IFS function in Excel 2010

  1. #1
    Registered User
    Join Date
    04-04-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    Office 2010
    Posts
    2

    Question Creating an IFS function in Excel 2010

    I have a set of data that im trying to create almost a cross between Vlookup and If function. If i have a set of data with headings as:

    | Code | Packsize | Unit Price | Quantity >100 | Quantity >500 | Quantity >1000 |
    ----------------------------------------------------------------------------------------------
    | BER01 | 12 | $4,50 | $4 | $3 | $2 |
    | BER02 | 24 | $7,50 | $7 | $6 | $5 |

    So i will try break down what im trying to do, on one sheet i have the above headings with the relevant data. On another sheet i have the first column with the code, the second column with the quantity i want and the third column with the price. Now i am trying to figure out how i can make the price pop up that if i choose the code BER01 that it will show the price $4,50. But if i put a quantity greater then 100 the in will show $4, but if i put a quantity greater then 500 it will show $3 and so on. But this is where im getting stuck. On the first sheet that only has the 3 columns that im trying to create, the column with the code is not fixed, it has a range of up to 100 items that i can choose from a list that i created through the list validation method. So this would need to be based on any code i select, it will look up that code in the second sheet where all my data is and the give me the respective price according to the quantity that i have put in.

    This is a formula i tried to create but keep #NAME? as my output.

    =IF(A20=””;””;IF(E20>=3500;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;14;FALSE);IF(3500>E20>=2000;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;13;FALSE);IF(2000>E20>=500;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;12;FALSE);IF(500>E20>=400;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;11;FALSE);IF(400>E20>=300;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;10;FALSE);IF(300>E20>=168;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;17;FALSE);IF(168>E20>=126;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;16;FALSE);IF(126>E20>=100;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;9;FALSE);IF(100>E20>=91;VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;15;FALSE);VLOOKUP(A20;'Supplier Data Sheet'!$A$3:$Q$100;3;FALSE)))))))))))

    Please help!

    Thanks
    Last edited by JarredFuchs; 04-04-2017 at 05:01 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,778

    Re: Creating an IFS function in Excel 2010

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Please note that the Paperclip icon does not work.

    This:

    ... IF(3500>E20>=2000; ...

    is not a valid Excel expression. You have already tested for E20>=3500, so you don't need to test for it again.

    Hope this helps.

    Pete

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Creating an IFS function in Excel 2010

    change =IF(A20=””;””;.... to =IF(A20="";"";...
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,722

    Re: Creating an IFS function in Excel 2010

    See attached:

    in J3

    =INDEX($C$3:$F$4,MATCH($H3,$A$3:$A$4,0),MATCH($I3,$C$2:$F$2,1))
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Creating an IFS function in Excel 2010

    I haven't read your description of the problem fully but you can replace your current formula which is incorrect with this

    =IF(A20="";"";VLOOKUP(A20;'"Supplier Data Sheet'!$A$3:$Q$100;LOOKUP(E20;{-1E99;91;100;126;168;300;400;500;2000;3500};{3;15;9;16;17;10;11;12;13;14})))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  6. #6
    Registered User
    Join Date
    04-04-2017
    Location
    Cape Town, South Africa
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Creating an IFS function in Excel 2010

    Thanks @JohnTopley, this seems to work even when you validate the yellow column you created! very helpful!
    Last edited by JarredFuchs; 04-04-2017 at 06:06 AM.

+ 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. Creating excel 2010 graph
    By Murr007 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-02-2015, 11:48 AM
  2. Creating a Bell Curve in Excel 2010
    By Abiananth1 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-31-2014, 01:13 AM
  3. Creating a Tournament Bracket in Excel 2010
    By JackKieser in forum Excel General
    Replies: 1
    Last Post: 11-11-2013, 08:24 PM
  4. [SOLVED] Creating an Add-In of Existing Workbook in Excel 2010
    By DakotaRoss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2013, 09:08 AM
  5. Excel 2010 : Spline or Interpolate function for 2010
    By jpjpj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 12:52 AM
  6. Add-in creating links in Excel 2010
    By Vafur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2012, 12:12 PM
  7. Creating function in Excel 2010
    By JohnnyWalker in forum Excel General
    Replies: 4
    Last Post: 05-30-2012, 05:53 PM

Tags for this Thread

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