creating a quote looking up from tables

[andyell]

I am trying to create a quote system using excel, and need to be able to select from
a range of quantities and then
select from a range of colours

I have tried using index and match functions but this will only work if the quantites are the same as in the table.but i need to be able to select from a range of quantities
0-15
16-24 etc

I cant use nested if statements as there are 12 quantity groups

thanks in advance

[Max]

One play ..

A sample construct is available at:
http://www.savefile.com/files/5747910
Quote System 2 variables Tiered Qty by Colour.xls

Assume the reference price table below is in sheet: X, wihin A1:D4

Qty red blue green
1 $10.00 $20.00 $15.00
16 $20.00 $50.00 $35.00
25 $30.00 $60.00 $50.00

and in another sheet: Y (say)
the quantities and colours
will be input / selected in A2 and B2 down

Qty Colour Quote
12 Green
25 Red
5 Blue
17 Red
etc

Put in C2, then copy down:
=IF(OR(A2="",B2=""),"",INDEX(X!$A$1:$D$4,MATCH(A2,X!A:A,1),MATCH(B2,X!$1:$1,0)))

Format col C as currency

Col C will retrieve the quotes from the price table in X

Qty Colour Quote
12 Green $15.00
25 Red $30.00
5 Blue $20.00
17 Red $20.00
etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"andyell" wrote:
>
> I am trying to create a quote system using excel, and need to be able to
> select from
> a range of quantities and then
> select from a range of colours
>
> I have tried using index and match functions but this will only work if
> the quantites are the same as in the table.but i need to be able to
> select from a range of quantities
> 0-15
> 16-24 etc
>
> I cant use nested if statements as there are 12 quantity groups
>
> thanks in advance
>
>
> --
> andyell
> ------------------------------------------------------------------------
> andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
> View this thread: http://www.excelforum.com/showthread...hreadid=550820
>
>