I have a scenario where I am looking up supplier prices in one spreadsheet and putting them into another in order to produce a client quotation. In one tab I have information on our suppliers and their discount structure and on the other I have their prices.
Some of our suppliers offer us discount based on quantities purchased. I am trying to understand therefore if I can do a Vlook up based on two parameters? Hopefully I can explain it!...
Let’s say my supplier is called ‘Ford’ and I was buying 10 cars. From the below you will see I would get 20% discount.
Spreadsheet name = TBL_Supplier_Costs
In my quotation spreadsheet I would like to achieve the following:
I hope someone can understand that! Please let me know if I need to explain anything else. Thank you
Some of our suppliers offer us discount based on quantities purchased. I am trying to understand therefore if I can do a Vlook up based on two parameters? Hopefully I can explain it!...
Let’s say my supplier is called ‘Ford’ and I was buying 10 cars. From the below you will see I would get 20% discount.
Spreadsheet name = TBL_Supplier_Costs
| A | B | C |
| Supplier | Quantity Break Point | Discount |
| Ford | 1 | 0% |
| Ford | 5 | 10% |
| Ford | 10 | 20% |
| Ford | 20 | 30% |
| Ford | 50 | 40% |
| Vauxhal | 1 | 0% |
| Vauxhal | 5 | 5% |
| Vauxhal | 10 | 7% |
In my quotation spreadsheet I would like to achieve the following:
| X | Y | Z | AA |
| Supplier | Qty | Supplier Discount | Price |
| Ford | 10 | Using cell X look up the supplier in the spreadsheet called [Tbl_Supplier_Costs.xlsx]Qty_Discounts! And using cell Y lookup what the discount would be based on that quantity | I’m working this bit out. |
I hope someone can understand that! Please let me know if I need to explain anything else. Thank you
