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.

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.

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.


.

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.


.

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