finding value from table

[andyell]

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

[Marcelo]

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
>
>

[Allllen]

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
> >
> >

[andyell]

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

[paul]

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
[email protected]
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
>
>