|
finding value from table
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 |
finding value from table
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 |
finding value from table
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 |
finding value from table
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 |
| All times are GMT +1. The time now is 06:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com