Need appropriate formula that derives a value for 2 lookups within a range

[mattlaco]

Hello,

I am trying to find out the correct formula and structure to derive the appropriate level of discount (Level 0, Level 1, Level 2, Level 3), based on the quantity and the discount %.

I have attached a sample table that shows four levels of discount. Both the quantity purchased and the discount % will be input into another table.

Example: If I am purchasing 11 Widgets at a 27% discount, that would be a Level 1 Discount. What is the best way to automate that with a formula? Please see discount table attached.

Thanks


Sample Pricing Table.jpg

[etaf]

this will help with the requirement
http://skp.mvps.org/xl00002.htm
by using a 1 instead of a 0 in the match - it should find the lowest match

if you do the following - it would help a lot more than an image

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Jamie.Reis]

See the attached example.

The two highlighted items are what you'd want to modify.
The discount item is going to be a validation list only allowing you to select a discount percentage from the available options in the table.

That logic:

Please Login or Register  to view this content.

The logic that determines the discount level:

Please Login or Register  to view this content.

Thanks,
Jamie