Price Grid [Index/Lookup/Match]

Hi all,
I have a simple price grid: The rows are by "height" and the columns are by "width". If some one enters a height and a width in two seperate cells, I want another cell to find the proper cross reference cell (price). I am using an Index formula and it works ok except one thing: My height and width numbers in the price grid are incremental, its like this:
_________________________________________________
Width: 39 54 78 90 118
Height:
36 $20 $36 $52
48 $24 $38 $59
60 $27 $43
72
84
_________________________________________________
So if a user enters a width of 42 and a height of 42, I want the price returned to be $38 (in other words rounded up to the next cell). If I use a typical Index/Match formula and enter the match_type as 1, it would return $20, not $38.

How can I get my index formula to push the result up to the next grid position (or round up)?

Here is the formula I have been using:
=INDEX(Flocke,MATCH(F6,E17:E27,1),MATCH(F5,F15:M15,1))



Thanks!

Originally Posted by squiggler47

In your case :-

=INDEX(Flocke,MIN(COUNT(G17:K17),COUNT(G17:K17)-COUNTIF(G17:K17,">="&F10)+1),MIN(COUNT(F18:F22),COUNT(F18:F22)-COUNTIF(F18:F22,">="&F11)+1))

Where G17:k17 is the width sizes

F18:F22 is the heights

F10, F11 are the Width and Height to look up!

and i assume flocke is the prices!

thanks, this is getting closer. I attached the file with this formula in action, but its resulting in slightly wrong prices. I do really appreciate your help!!!!!!! Thanks for your time so far!
Jared

Originally Posted by squiggler47

And interestingly, it looks like your pricelists are for Blinds/Curtains which is why I wrote the price list any way, it was for showing retailers how to price from a grid. We had several try to claim that pricing was done from the gridssquare below the sizes not up to the next size!

Which is where this visual pricing grid came from, the other sheet I made has a 1300 character formula for prices, just because of the rules they made to price beyond the grid!

also note if your grids are all the same size :-

=INDEX(Flocke,MIN(11,11-COUNTIF(E12:E22,">="&F6)+1),MIN(8,8-COUNTIF(F11:M11,">="&F5)+1))

would make the formula easier to maintain!

A million thank yous to both of you!!! And we have the same problem with our retailers rounding down. We are a manufacturer of shades and this has been plaguing us for years. Its one of the hardest industries to price for, square foot pricing just doesnt work (unless selling plantation shutters).

I have a similar challenge and this solution has worked well! One question though, what happens if the pricing grids are on different sheets? I would like to use a dropdown to select a product, then pick the height/drop for that specific product, then lookup the price, which will be on a separate sheet. The pricing grids are not necessarily all the same size.