finding value from table

A

andyell

i need to select a price from a table where the varibles a re price an
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 val
to be inserted should be €1.36
Any help greatfully recieved

And
 
M

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

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

andyell

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

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
 
Top