# Creating an IFS function in Excel 2010

1. ## 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)))))))))))

Thanks  Register To Reply

2. ## 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  Register To Reply

3. ## Re: Creating an IFS function in Excel 2010

change =IF(A20=””;””;.... to =IF(A20="";"";...  Register To Reply

4. ## 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))  Register To Reply

5. ## 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})))  Register To Reply

6. ## 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!  Register To Reply

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