Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Tables
I'm trying to develop a formula to extract info from a
table. The vertical column is a range that corresponds to a calculation in the table body. I must have a calculated number be able to pick the correct range and then the lookup functions would be able to pick the associated formula. E.g. if cell A1 = 15.3, I have a column such as: 0 - 10 xxx 10.01 - 15 xxx 15.01 - 20 xxx where xxx is the formula. How can I get it to pick the third row formula without a nest of if statements (I have many lines)? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Tables
Kevin,
Setup your table as, for example M1: 0 N1: xxx M2: 10.01 N2: xxx M3:15.01 N3: xxxx etc. In B1, use =VLOOKUP(A1,$M$1:$N$10,2,FALSE) I am assuming that you want this formula to be echoed on screen, not that it is a worksheet formula that you want to automatically run. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... I'm trying to develop a formula to extract info from a table. The vertical column is a range that corresponds to a calculation in the table body. I must have a calculated number be able to pick the correct range and then the lookup functions would be able to pick the associated formula. E.g. if cell A1 = 15.3, I have a column such as: 0 - 10 xxx 10.01 - 15 xxx 15.01 - 20 xxx where xxx is the formula. How can I get it to pick the third row formula without a nest of if statements (I have many lines)? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Tables
No, I want the formula in the table to run once the
criteria picks the correct reference. -----Original Message----- Kevin, Setup your table as, for example M1: 0 N1: xxx M2: 10.01 N2: xxx M3:15.01 N3: xxxx etc. In B1, use =VLOOKUP(A1,$M$1:$N$10,2,FALSE) I am assuming that you want this formula to be echoed on screen, not that it is a worksheet formula that you want to automatically run. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... I'm trying to develop a formula to extract info from a table. The vertical column is a range that corresponds to a calculation in the table body. I must have a calculated number be able to pick the correct range and then the lookup functions would be able to pick the associated formula. E.g. if cell A1 = 15.3, I have a column such as: 0 - 10 xxx 10.01 - 15 xxx 15.01 - 20 xxx where xxx is the formula. How can I get it to pick the third row formula without a nest of if statements (I have many lines)? Thanks. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Tables
Kevin,
Thought you might. I think you need to combine VLOOKUP with a UDF for this. The UDF would go in a normal code module, and would look like Function MyEval(formula) MyEval = Evaluate(formula) End Function The worksheet formula then becomes =MyEval(VLOOKUP(E1,I1:J2,2,FALSE)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) wrote in message ... No, I want the formula in the table to run once the criteria picks the correct reference. -----Original Message----- Kevin, Setup your table as, for example M1: 0 N1: xxx M2: 10.01 N2: xxx M3:15.01 N3: xxxx etc. In B1, use =VLOOKUP(A1,$M$1:$N$10,2,FALSE) I am assuming that you want this formula to be echoed on screen, not that it is a worksheet formula that you want to automatically run. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Kevin" wrote in message ... I'm trying to develop a formula to extract info from a table. The vertical column is a range that corresponds to a calculation in the table body. I must have a calculated number be able to pick the correct range and then the lookup functions would be able to pick the associated formula. E.g. if cell A1 = 15.3, I have a column such as: 0 - 10 xxx 10.01 - 15 xxx 15.01 - 20 xxx where xxx is the formula. How can I get it to pick the third row formula without a nest of if statements (I have many lines)? Thanks. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Tables
the lookup formulas were originally designed to work with
tax tables (I think) anyway, if you take the ,false out of the formula, =vlookup(A1,Range,2) then the formula will look for the closest match without going over. 0 xxx 10 yyy 20 zzz =vlookup(5,range,2) will result in xxx. Make sure your lookup column (0,10,20) is sorted from lowest to highest. -----Original Message----- I'm trying to develop a formula to extract info from a table. The vertical column is a range that corresponds to a calculation in the table body. I must have a calculated number be able to pick the correct range and then the lookup functions would be able to pick the associated formula. E.g. if cell A1 = 15.3, I have a column such as: 0 - 10 xxx 10.01 - 15 xxx 15.01 - 20 xxx where xxx is the formula. How can I get it to pick the third row formula without a nest of if statements (I have many lines)? Thanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and tables | Excel Worksheet Functions | |||
Lookup tables? | Excel Worksheet Functions | |||
Lookup Tables | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions | |||
Lookup tables | Excel Worksheet Functions |