Need help creating a pricing matrix based on width and height

**alisssa** · 03-10-2015, 05:27 PM

I've created a pricing matrix based on width and height, width of item along the top, height of item down the side - with all the different prices in between. How do I go about
creating a separate quotation sheet where the user fills in box 1 (width of item), fills in box 2 (height of item) and excel refers to the table and automatically tells him the price?
Please help!
Thanks,
Alisa

**daffodil11** · 03-10-2015, 05:36 PM

Let's go to imagination land where this spreadsheet exists!

On Sheet2 is your magical matrix. B1:M1 = width, A2:A10 = height

On Sheet1 you have A1 = width
On Sheet1 you have A2 = height

A3, the price =

=SUMPRODUCT((Sheet2!B1:M1=A1)*(Sheet2!A2:A10=A2)*(Sheet2!B2:M10))

or

=INDEX(Sheet2!B2:M10,MATCH(A1,Sheet2!B1:M1,0),MATCH(A2,Sheet2!A2:A10,0))

**alisssa** · 03-10-2015, 05:55 PM

Thanks for the quick answer!
Actually is something like this:

I have the width and the height, and the price must be calculated as following:
Width x height x 90

Waiting for an answer!
I also need to know where in the excel document do I add the formula

**daffodil11** · 03-10-2015, 06:01 PM

I told you where.

You type height in A1

You type width in A2

The formula can go ANYWHERE. In my post above I put it in A3.
The only difference would be that you multiply the result *90.

=SUMPRODUCT((Sheet2!B1:M1=A1)*(Sheet2!A2:A10=A2)*(Sheet2!B2:M10))*90

**alisssa** · 03-10-2015, 06:25 PM

I tested it, it really works!

My matrix in Sheet2 looks like this:

0 50 55 60 65 70 75 80 85 90 95 100 105
50 55 60 65 70 75 80 85 90 95 100 105 100
55 60 65 70 75 80 85 90 95 100 105 100 95
60 65 70 75 80 85 90 95 100 105 100 95 90
65 70 75 80 85 90 95 100 105 100 95 90 85
70 75 80 85 90 95 100 105 100 95 90 85 80
75 80 85 90 95 100 105 100 95 90 85 80 75
80 85 90 95 100 105 100 95 90 85 80 75 70
85 90 95 100 105 100 95 90 85 80 75 70 65
90 95 100 105 100 95 90 85 80 75 70 65 60
95 100 105 100 95 90 85 80 75 70 65 60 55
100 105 100 95 90 85 80 75 70 65 60 55 50
105 100 95 90 85 80 75 70 65 60 55 50 0

Now there is something else: I need to introduce in the A1 (width) and A2 (height) in Sheet1 an amount such as, for example, in A1 (width) to be 56 and in A2 (height) let's say... 49
The formula won't return anything for these values between, only for the exact values that are in the matrix.

My question is: is there a solution for my "problem"?

**daffodil11** · 03-10-2015, 06:37 PM

Oh everything is possible. "We are the music makers, and we are the dreamers of dreams."

Just take the 0's out of the MATCH's and the formula will approximate instead of look for an exact match.

=INDEX(Sheet2!B2:M13,MATCH(A1,Sheet2!B1:M1),MATCH(A2,Sheet2!A2:A13))*90

Furthermore, you can encapsulate the whole thing in IFERROR to create a default output for when there is no match, such as "" to just leave the cell blank.

=IFERROR(INDEX(Sheet2!B2:M13,MATCH(A1,Sheet2!B1:M1),MATCH(A2,Sheet2!A2:A13))*90,"")

I created an attachment of it working on my end, with ranges highlighted as the criteria is applying in the formula.

I also threw in a unicorn, because spreadsheets need more unicorns.