i need to select a price from a table where the varibles a re price and
quantity
Qty 1 2 3 4 5 6 7 8
15 €57.14 €57.14 €57.14 €57.14 €57.14 €57.14 €57.14 €57.14
25 €3.21 €3.71 €4.14 €4.57 €5.43 €6.00 €0.00 €0.00
50 €2.50 €3.00 €3.36 €3.93 €4.43 €5.00 €0.00 €0.00
100 €2.07 €2.36 €2.57 €2.71 €3.07 €3.21 €0.00 €0.00
250 €1.00 €1.21 €1.56 €1.90 €2.24 €2.59 €2.93 €3.27
500 €0.87 €1.04 €1.36 €1.56 €1.80 €2.09 €2.33 €2.59
1,000 €0.79 €0.94 €1.30 €1.50 €1.71 €2.00 €2.33 €2.57
2,500 €0.63 €0.74 €0.83 €0.94 €1.04 €1.13 €1.26 €1.36
5,000 €0.57 €0.63 €0.69 €0.74 €0.77 €0.83 €0.89 €0.94
10,000 €0.54 €0.56 €0.59 €0.61 €0.63 €0.64 €0.67 €0.69
100,000 €0.54 €0.59 €0.61 €0.64 €0.67 €0.70 €0.71 €0.73
so if the number entered is 500 and the other parameter is 3 the valu
to be inserted should be €1.36
Any help greatfully recieved

Andy


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550482

Hi andyell

assuming that you data base is in range A1:I12, on single way to do it is

put on the C17 the qty (500 for eg)
on the C18 the other factor (3 for eg)

and the formula could be =vlookup(c17;$A$1:$I$12,(C18+1),0)

Hope this helps
regards from Brazil
Marcelo






"andyell" escreveu:


i need to select a price from a table where the varibles a re price and
quantity
Qty 1 2 3 4 5 6 7 8
15 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14
25 ‚¬3.21 ‚¬3.71 ‚¬4.14 ‚¬4.57 ‚¬5.43 ‚¬6.00 ‚¬0.00 ‚¬0.00
50 ‚¬2.50 ‚¬3.00 ‚¬3.36 ‚¬3.93 ‚¬4.43 ‚¬5.00 ‚¬0.00 ‚¬0.00
100 ‚¬2.07 ‚¬2.36 ‚¬2.57 ‚¬2.71 ‚¬3.07 ‚¬3.21 ‚¬0.00 ‚¬0.00
250 ‚¬1.00 ‚¬1.21 ‚¬1.56 ‚¬1.90 ‚¬2.24 ‚¬2.59 ‚¬2.93 ‚¬3.27
500 ‚¬0.87 ‚¬1.04 ‚¬1.36 ‚¬1.56 ‚¬1.80 ‚¬2.09 ‚¬2.33 ‚¬2.59
1,000 ‚¬0.79 ‚¬0.94 ‚¬1.30 ‚¬1.50 ‚¬1.71 ‚¬2.00 ‚¬2.33 ‚¬2.57
2,500 ‚¬0.63 ‚¬0.74 ‚¬0.83 ‚¬0.94 ‚¬1.04 ‚¬1.13 ‚¬1.26 ‚¬1.36
5,000 ‚¬0.57 ‚¬0.63 ‚¬0.69 ‚¬0.74 ‚¬0.77 ‚¬0.83 ‚¬0.89 ‚¬0.94
10,000 ‚¬0.54 ‚¬0.56 ‚¬0.59 ‚¬0.61 ‚¬0.63 ‚¬0.64 ‚¬0.67 ‚¬0.69
100,000 ‚¬0.54 ‚¬0.59 ‚¬0.61 ‚¬0.64 ‚¬0.67 ‚¬0.70 ‚¬0.71 ‚¬0.73
so if the number entered is 500 and the other parameter is 3 the valu
to be inserted should be ‚¬1.36
Any help greatfully recieved

Andy


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550482

andyell,

Here is a more flexible way, how about:
=INDEX($A$1:$I$12,MATCH(C18,1:1,0),MATCH(C17,A:A,0 ))
--
Allllen


"Marcelo" wrote:

Hi andyell

assuming that you data base is in range A1:I12, on single way to do it is

put on the C17 the qty (500 for eg)
on the C18 the other factor (3 for eg)

and the formula could be =vlookup(c17;$A$1:$I$12,(C18+1),0)

Hope this helps
regards from Brazil
Marcelo






"andyell" escreveu:


i need to select a price from a table where the varibles a re price and
quantity
Qty 1 2 3 4 5 6 7 8
15 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14
25 ‚¬3.21 ‚¬3.71 ‚¬4.14 ‚¬4.57 ‚¬5.43 ‚¬6.00 ‚¬0.00 ‚¬0.00
50 ‚¬2.50 ‚¬3.00 ‚¬3.36 ‚¬3.93 ‚¬4.43 ‚¬5.00 ‚¬0.00 ‚¬0.00
100 ‚¬2.07 ‚¬2.36 ‚¬2.57 ‚¬2.71 ‚¬3.07 ‚¬3.21 ‚¬0.00 ‚¬0.00
250 ‚¬1.00 ‚¬1.21 ‚¬1.56 ‚¬1.90 ‚¬2.24 ‚¬2.59 ‚¬2.93 ‚¬3.27
500 ‚¬0.87 ‚¬1.04 ‚¬1.36 ‚¬1.56 ‚¬1.80 ‚¬2.09 ‚¬2.33 ‚¬2.59
1,000 ‚¬0.79 ‚¬0.94 ‚¬1.30 ‚¬1.50 ‚¬1.71 ‚¬2.00 ‚¬2.33 ‚¬2.57
2,500 ‚¬0.63 ‚¬0.74 ‚¬0.83 ‚¬0.94 ‚¬1.04 ‚¬1.13 ‚¬1.26 ‚¬1.36
5,000 ‚¬0.57 ‚¬0.63 ‚¬0.69 ‚¬0.74 ‚¬0.77 ‚¬0.83 ‚¬0.89 ‚¬0.94
10,000 ‚¬0.54 ‚¬0.56 ‚¬0.59 ‚¬0.61 ‚¬0.63 ‚¬0.64 ‚¬0.67 ‚¬0.69
100,000 ‚¬0.54 ‚¬0.59 ‚¬0.61 ‚¬0.64 ‚¬0.67 ‚¬0.70 ‚¬0.71 ‚¬0.73
so if the number entered is 500 and the other parameter is 3 the valu
to be inserted should be ‚¬1.36
Any help greatfully recieved

Andy


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550482

sorry if i was unclear i am trying to prepare a spreadsheet where users
can prepare ther own qutes so if they enter the quantity and number of
colours the spreadsheet will do the rest
so the 250 selects the row with those values and if they enter 3
colours then the formula should select the correct value €1.56 from the
table above. i think i need to use index and match functions but just
not sure the format


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550482

just a suggestion,dont format your table as currency,makes it hard to
read,format the answer as currency :),i would use a vloolup(match())
combination too
--
paul

remove nospam for email addy!



"andyell" wrote:


i need to select a price from a table where the varibles a re price and
quantity
Qty 1 2 3 4 5 6 7 8
15 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14 ‚¬57.14
25 ‚¬3.21 ‚¬3.71 ‚¬4.14 ‚¬4.57 ‚¬5.43 ‚¬6.00 ‚¬0.00 ‚¬0.00
50 ‚¬2.50 ‚¬3.00 ‚¬3.36 ‚¬3.93 ‚¬4.43 ‚¬5.00 ‚¬0.00 ‚¬0.00
100 ‚¬2.07 ‚¬2.36 ‚¬2.57 ‚¬2.71 ‚¬3.07 ‚¬3.21 ‚¬0.00 ‚¬0.00
250 ‚¬1.00 ‚¬1.21 ‚¬1.56 ‚¬1.90 ‚¬2.24 ‚¬2.59 ‚¬2.93 ‚¬3.27
500 ‚¬0.87 ‚¬1.04 ‚¬1.36 ‚¬1.56 ‚¬1.80 ‚¬2.09 ‚¬2.33 ‚¬2.59
1,000 ‚¬0.79 ‚¬0.94 ‚¬1.30 ‚¬1.50 ‚¬1.71 ‚¬2.00 ‚¬2.33 ‚¬2.57
2,500 ‚¬0.63 ‚¬0.74 ‚¬0.83 ‚¬0.94 ‚¬1.04 ‚¬1.13 ‚¬1.26 ‚¬1.36
5,000 ‚¬0.57 ‚¬0.63 ‚¬0.69 ‚¬0.74 ‚¬0.77 ‚¬0.83 ‚¬0.89 ‚¬0.94
10,000 ‚¬0.54 ‚¬0.56 ‚¬0.59 ‚¬0.61 ‚¬0.63 ‚¬0.64 ‚¬0.67 ‚¬0.69
100,000 ‚¬0.54 ‚¬0.59 ‚¬0.61 ‚¬0.64 ‚¬0.67 ‚¬0.70 ‚¬0.71 ‚¬0.73
so if the number entered is 500 and the other parameter is 3 the valu
to be inserted should be ‚¬1.36
Any help greatfully recieved

Andy


--
andyell
------------------------------------------------------------------------
andyell's Profile: http://www.excelforum.com/member.php...o&userid=35272
View this thread: http://www.excelforum.com/showthread...hreadid=550482