Excel lookup to return formula

Question

I have a two-column lookup table something like:

    Col1    Col2
    tran1   =qty/1000
    tran2   =qty/2000
    tran3   =price+10
    

where qty and price are named ranges.

I want to use Vlookup (or whatever else may work) to populate a cell with the formula in column 2, rather than the value.

Let's suppose A1 contains tran1 and B1 = 2500. B1 is the named range qty. I want to lookup the formula in Col2 of my lookup table and insert the formula into cell C1, so that cell C1 displays 2.5.

Using Excel 2010.

I'm struggling to find a way to do this.

Answer 1

Say we have a lookup table in columns G and H like:

We want to enter a word in cell A1 and the appropriate formula appear in cell B1.

Enter the following Event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range

    If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
    Set r = Range("G1:G4").Find(What:=Target.Value)
    If r Is Nothing Then Exit Sub
    Application.EnableEvents = False
        Target.Offset(0, 1).Formula = r.Offset(0, 1).Formula
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!