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
Bookmarks