Lookup Tables

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

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.
 
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)
 
No, I want the formula in the table to run once the
criteria picks the correct reference.
 
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)
 
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.
 
Back
Top