Formula help

Closed
BeatsA Posts 1 Registration date Friday August 2, 2013 Status Member Last seen August 2, 2013 - Aug 2, 2013 at 10:22 AM
russp5 Posts 4 Registration date Friday August 2, 2013 Status Member Last seen August 2, 2013 - Aug 2, 2013 at 01:24 PM
I have a table of window blind size prices. The price changes due to the width and drop of the blind. The headings along the top of 450mm, 600mm, 750mm 900mm etc... means that if a blind is 760 wide then the 750mm column is too small so I choose the 900mm column. Then the dop can be 900mm, 1600mm, 2000mm. So if that blind was 1760 drop then it's the 2000mm drop row.

So that's how the table is organised, 14 different columns for blind width (each column means "Upto" this size) and 3 rows for drop (each row means "Upto" this size).

So on a spreadsheet I have 4 headings. "Window", "Width", "Drop", "Price".

So under window, I'll enter "Lounge" then 760 under Width and 1760 drop. In the price cell, I need a formula that takes the width and drop size and locates the column and row that will cater for those sizes and display the price. I can use V and H Lookup but it's trying to figure out how for it to find which headings will cover that size.

I know of two long winded ways of doing it but does someone know a much quicker simple way. Thanks.

1 response

russp5 Posts 4 Registration date Friday August 2, 2013 Status Member Last seen August 2, 2013 1
Aug 2, 2013 at 01:24 PM
I would first start with a MATCH formula for the column (width) and row (drop). This will get the coordinates for the price table by referencing the first row and column with values.

Next you can use the value in an OFFSET function to return the price based on the coordinates.
0