Lookup table using Formula in EXCEL

Question

I'm building a spreadsheet, which uses different formula in cells. Those formulas change based on formula ID is called. For example, the formula lookup table looks like this:

Formula ID|Formula
__________+________________
    1     |  y=x+2
__________+________________
    2     |  y=x^2 - 34
__________+________________
    3     |  ...
__________+________________
    ...   |  ...
__________+________________

My main worksheet looks like this:

   |   A   |   B   |   C
___+_______+_______+________
 1 |   2   |   *** | =if(A1=2, use formula #2, which is "=B1^2-34", "")
                     =if(A1=1, use formula #1, which is "=B1+2", "")
___+_______+_______+________
 2 |   1   |   *** | =if(A1=2, use formula #2, which is "=B2^2-34", "")
                     =if(A1=1, use formula #1, which is "=B2+2", "")
___+_______+_______+________
 3 |   2   |   *** | =if(A1=2, use formula #2, which is "=B3^2-34", "")
                     =if(A1=1, use formula #1, which is "=B3+2", "")

"A" column displays formula ID. "B" column is the input of "x". "C" column is the function "y".

I could only find how to use the value in the lookup table, not formula. Please help. Thanks very much.

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-01-24T15:49:06

You can use simple user defined function:

Function eval(formula As String, r As Range)
    If Left(formula, 1) = "y" Then
        formula = Right(formula, Len(formula) - 1)
    End If
    eval = Evaluate(Replace(formula, "x", r.Address))
End Function

and then call it in C1 like this:

=eval(VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0),B1)

where Sheet1!$A$1:$B$100 stores your table with formulas

Lookup table using Formula in EXCEL

2 Answers