Yufan Chen
New Member
Hello,
I'm attempting to make a pricing chart and wasn't able to find a method or set of formulas that would allow me to achieve what I'm trying to do. The closest I've come is using a multiple criteria formula using the Index and Match combination.
I manufacture and sell picture frames and want to be able to enter two custom sizes (two different cells), the width and the length of a picture frame. I want the result or lookup (1 cell) to refer to a chart generating the 'respective' price using the two size columns on the chart.
I've attached an image that shows the type of chart I've made, and better show what I'm trying to do as I explain below:
The sizes entered don't have to match exactly the sizes listed on the price chart, but if one of the two sizes entered are a fraction larger than a chart-listed size, then the 'lookup' should round up and go to the next size up. (i.e. Enter 10 x 36.125"; Sizes in listed on chart in columns is 30 x 36" and 32 x 40" ----> Then the price generated will come from the row 32 x 40") Also I want the look up to favor the larger of the two sizes (width vs. length) for choosing which column and then following row to find the price. In the last example the 36.125 was the larger size so the 10 almost became irrelevant. The time where both sizes would matter, is if it's an exact match to the sizes on the chart.
I hope this wasn't too confusing. Any suggested solutions offered is greatly appreciated. Thank you!!
-Yufan
I'm attempting to make a pricing chart and wasn't able to find a method or set of formulas that would allow me to achieve what I'm trying to do. The closest I've come is using a multiple criteria formula using the Index and Match combination.
I manufacture and sell picture frames and want to be able to enter two custom sizes (two different cells), the width and the length of a picture frame. I want the result or lookup (1 cell) to refer to a chart generating the 'respective' price using the two size columns on the chart.
I've attached an image that shows the type of chart I've made, and better show what I'm trying to do as I explain below:
The sizes entered don't have to match exactly the sizes listed on the price chart, but if one of the two sizes entered are a fraction larger than a chart-listed size, then the 'lookup' should round up and go to the next size up. (i.e. Enter 10 x 36.125"; Sizes in listed on chart in columns is 30 x 36" and 32 x 40" ----> Then the price generated will come from the row 32 x 40") Also I want the look up to favor the larger of the two sizes (width vs. length) for choosing which column and then following row to find the price. In the last example the 36.125 was the larger size so the 10 almost became irrelevant. The time where both sizes would matter, is if it's an exact match to the sizes on the chart.
I hope this wasn't too confusing. Any suggested solutions offered is greatly appreciated. Thank you!!
-Yufan
