Excel lookup to return formula -

i have two-column lookup table like:

     col1    col2     tran1   =qty/1000     tran2   =qty/2000     tran3   =price+10     

qty , price named ranges.

i want use vlookup (or whatever else may work) populate cell formula in column 2, rather value.

let's suppose a1 contains tran1 , b1 = 2500. b1 named range qty. want lookup formula in col2 of lookup table , insert formula cell c1, cell c1 displays 2.5.

using excel 2010.

i'm struggling find way this.

say have lookup table in columns g , h like:

we want enter word in cell a1 , appropriate formula appear in cell b1.

enter following event macro in worksheet code area:

private sub worksheet_change(byval target range)     dim r range      if intersect(range("a1"), target) nothing exit sub     set r = range("g1:g4").find(what:=target.value)     if r nothing exit sub     application.enableevents = false         target.offset(0, 1).formula = r.offset(0, 1).formula     application.enableevents = true end sub 

because worksheet code, easy install , automatic use:

1. right-click tab name near bottom of excel window
2. select view code - brings vbe window
3. paste stuff in , close vbe window

if have concerns, first try on trial worksheet.

if save workbook, macro saved it. if using version of excel later 2003, must save file .xlsm rather .xlsx

to remove macro:

1. bring vbe windows above
2. clear code out
3. close vbe window

to learn more 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 event macros (worksheet code), see:

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

macros must enabled work!