vlookups returning formulas - not values - in excel

[Buckshot]

I have a long list of customers. Each of them have a unique pricing formula
associated with their account. On my "Tab1," I have written formulas to
return the right commission by customer for each transaction my company is
party to. (I wrote the formulas to calculate based on a plug value in column
"c". The pricing formulas are each "IF" or "LOOKUP" formulas.)

I have a second tab ("Tab2") that is a long list of thousands of
transactions. (The value of each transaction is also located in column "c").
I wrote a VLOOKUP to refer to the customer commission formulas on Tab1. The
vlookup successfully returns the VALUE in "Tab1"... but I am hoping to write
it in such a way that it returns the FORMULA. That way, I can actually
calculate the company commission by transaction by customer. (Right now,
with my vlookup returning the VALUE instead of returning the FORMULA, my data
calculations are worthless and based on the plug values in column "c" of
"Tab1".)

Any thoughts? Is there a better way to achieve this task? Thanks in
advance.

 

[Spiky]

Do you want it to return just the customer-specific portion of the
formula, or the entire IF formula as shown in Excel?

Generally, Excel's built-in functions can't do this. You'll need a
macro or UDF.

 

[Niek Otten]

What you require - processing a batch of data through one algorithm - can be
done using the Data>Table command. It's not really straightforward and to
give an example we'd need your data layout and probably some examples of
your formulas.
Look in HELP and post back in this same thread if you can't get it done, and
supply more details.

 

[Buckshot]

I want the vlookup to return the customer specific formula - each of which
are on the different rows of tab 1. I can get my vlookup to look at the
right cell, but when it returns an answer it returns the value that is in the
cell at that time, not the formula that is in the cell (that is the result
that i am trying to achieve).

 

[xlm via OfficeKB.com]

I have a long list of customers. Each of them have a unique pricing formula
associated with their account. On my "Tab1," I have written formulas to
return the right commission by customer for each transaction my company is
party to. (I wrote the formulas to calculate based on a plug value in column
"c". The pricing formulas are each "IF" or "LOOKUP" formulas.)

I have a second tab ("Tab2") that is a long list of thousands of
transactions. (The value of each transaction is also located in column "c").
I wrote a VLOOKUP to refer to the customer commission formulas on Tab1. The
vlookup successfully returns the VALUE in "Tab1"... but I am hoping to write
it in such a way that it returns the FORMULA. That way, I can actually
calculate the company commission by transaction by customer. (Right now,
with my vlookup returning the VALUE instead of returning the FORMULA, my data
calculations are worthless and based on the plug values in column "c" of
"Tab1".)

Any thoughts? Is there a better way to achieve this task? Thanks in
advance.

not sure if I understand your requirement.
Try using Excel built-in feature, Tool >> Option and check Formulas under
Window Option
This will show all the formulas in your sheet and you can uncheck it if you
want value.

HTH

 

[Spiky]

I want the vlookup to return the customer specific formula - each of which
are on the different rows of tab 1.  I can get my vlookup to look at the
right cell, but when it returns an answer it returns the value that is inthe
cell at that time, not the formula that is in the cell (that is the result
that i am trying to achieve).  

Yes, that is how Excel is supposed to work. It doesn't do what you are
asking with the built-in functions. You can just repeat the customer
formula in other cells.

A little more convoluted, but more what you are asking for:
EVAL or EVALUATE is a common UDF (search the internet for it) that
references an old, disabled Excel function that you aren't normally
allowed to use, anymore. (blame authors of viruses for Microsoft
removing this function) You have to reference it with a macro to make
it work.

You could take each customer formula and enter it as text in a column.
Then you can reference the formula whenever you wish to use it, plus
you can see it in text format easily if you ever need to. Then using
INDIRECT or CONCATENATE formulas combined with EVAL will perform
calculations based off the text version.