Finding values in Excel based on user input

[bjornltd]

Hi,
I have a problem that I could use some help solving.
I have an Excel workbook that calculate prices. The workbook has about 250 different products. All products are priced based on measurements, both width and height.
The user enters the product number, number of products, width and height. The measurements are entered in centimeters, with one decimal.

The products have different minimum and maximum measurements and different intervals in the pricelist. For example one product can have minimum width of 60 centimeters, max width of 300 centimeters and the pricelist are made with a new price for each 20 centimeters.
Another product may have min 290cm, max 700cm and price intervals on every 60cm. Also it might be price intervals like 290cm, 350cm, 470cm, 530cm, 590cm, 650cm and 700cm (where the last interval is only 50cm). Other intervals migth be on every 10cm, 20cm, 25cm, 50cm or 60cm.

What I aim to do is:

1. Make one sheet (hidden from other users) with all products listed down
2. Entering in culumn:
   1. A = Product number
   2. B = Width measure interval of the product (10cm, 20cm, 30cm, 50cm, 60cm....)
   3. C = Minimum width measurement
   4. D = Maximum width measurement
   5. E = Height measure interval
   6. F = Minimum height measurement
   7. G = Maximum height measurement

The goal is to find one formula that takes the users input product number, width and height. Rounds the width and height up to the next match and returns a with and height that I can use to look up the price in another sheet.
So if the user enters:

1. Product number 212
2. Width 530,5cm
3. Height 260cm

The pricelist for this product is: 290, 350, 470, 530, 590, 650 and 700cm. The return should then be 590. The same rule goes for the height.

Is there any way to solve this problem?
Any help would be greatly appreciated

 

[OaklandJim]

You've asked for a lot but not provided us with data so we'd have to make up our own fake data to figure this out. IDEALLY you could provide a link to the workbook so we could how it is structured.

At least you should provide data. Try using MrExcel's excellent addin that enables you to post portions of a worksheet -- called a mini sheet -- so we can see what the data looks like. See here: XL2BB Instructions.