Pricing from a table based on dimesions in 2 differnt cells (Excel

[Guest]

As above, I am trying to return (A) price value (within one cell) from values
contained in a table (which has widths running across the rows, heights
running down the columns. AND has values in 100's by the cell) based on
dimension values in two separate cells...
The format of the table values will not allow me to calculate the price
based on square metres or feet, but relies on arbitrary increments.
Furthermore, the dimension values in width are in Millimetres and the table
"Width" price changes in increments of 300 (17 options in Width prices. Too
many for IF function?)
Dimension value for height are in Milimetres and the table "Height" price
changes in increments of 300 also (8 options in Height prices)

SO;
Sheet 1 contains;

(@A20) WIDTH(0-5500, no decimal places)
(@C20) HEIGHT(0-3000, no decimal places)

(@H20) PRICE I want to insert rather than manually find from a
table..(formatted as currency)

Sheet 2 contains;
(Pricing table) lets say 2 by 2 price options to look up from table;
(@B2) (WIDTH to 2750, HEIGHT to 1500) price = 100
(@C2) (WIDTH to 5500, HEIGHT to 1500) price = 300
(@B3) (WIDTH to 2750, HEIGHT to 3000) price = 250
(@C3) (WIDTH to 5500, HEIGHT to 3000) price = 450

I would use the IF logic, but there are too many options (136 to be precise)
as stated but wonder if there is a way to return these values automatically.
As a self taught noob I am at a loss, hence my posting here for help with
this one...
Anyone?
Regards,
David

 

[Guest]

Set price table (A1:C3) as below: B1, C1 have widths, A2,A3 have heights


0 2751
0 100 300
1501 250 450

in H20:

=INDEX($A$1:$C$3,MATCH($C$20,$A$1:$A$3,1),MATCH($A$20,$A$1:$C$1,1))


If increments are 300, then your table "headings" will look like:

0 301 601 901 ......
0

301

601

901

etc

HTH

 

[Pete_UK]

I've assumed that you have widths going across sheet2 in cells B1 to
R1, eg 300 ... 600 ... 900 ... etc and that you have heights in cells
A2 to A11, eg 300, 600, 900 etc, and that you have price information
in the cells B2:R11. Enter the value for width in A20 of Sheet1 and
the value for height in C20, and put this formula in H20 (of the same
sheet):

=IF(OR(A20<Sheet2!B1,C20<Sheet2!A2),0,INDEX(Sheet2!
B2:R11,MATCH(C20,Sheet2!A2:A11),MATCH(A20,Sheet2!B1:R1)))

All one formula - be wary of line breaks. If A20 or C20 are empty or
less than the first incremental distance, then the formula will return
zero, otherwise it will give you the appropriate price.

Hope this helps.

Pete

 

[Guest]

Thank you both, it was actually a combination of both your answers which
answered the question, here was the answer on a single sheet (using
references rather than arrays)
=IF(AND($B$40>0,
$E$40>0),INDEX($AC$41:$AS$48,MATCH($E$40,$AB$41:$AB$49,1),MATCH($B$40,AC40:$AT$40,1)),)

Without the line break obviously, this allowed me to enter the two
dimensions without creating the lowest price in the table as soon as one was
entered in the dimension cells B40 - width & E40 - drop... (btw the pricing
table had 136 permutations of price based on 2 dimensions and was doing my
head in every time I was ordering as there are up to 8 articles per order!)

Once I had the above working I moved the price table to another sheet in the
book and changed the formula to;

=IF(AND($B$40>0,
$E$40>0),INDEX('C-Table'!D4:T11,MATCH($E$40,'C-Table'!C4:C12,1),MATCH($B$40,'C-Table'!D3:U3,1)),)

(1 of 8 above, and 8 of 8 below... Problem was, I didn't know how to copy
the formula from cell to cell, the manual editing took about half an hour!)

=IF(AND($B$54>0,
$E$54>0),INDEX('C-Table'!D4:T11,MATCH($E$54,'C-Table'!C4:C12,1),MATCH($B$54,'C-Table'!D3:U3,1)),)


However, for some reason I could't name the ranges within the table, or the
matching ranges for width and drop!

Once again, thank you both very much for your input.
Regards,
Dave