vllookup to run a formula

[jcc5018]

I am attempting to create a table that runs a different formula depending on the value in another column. The purpose is calculate final product prices depending on the type of product in different ways. So products may be calculated with a multiple times an area, while others may be a multiple of the cost of goods and so forth. I thought I would be able to do this with a vlookup function, but i cant get the string to run correctly.

I have 4 columns I am referencing. (Area, COGS, MType =multiples type, and Multiples)

I have a chart next to the table that has the formulas that I want to run. I have tried it with and without the specific table (table10) reference, and with and without an = in front of it, and with and without quotation marks around the string.

Mtype Formula
Area [@Multiplier]*[@Area]
Price [@[Cost of Goods]]*[@Multiplier]
Commission [@[Cost of Goods]]+[@Multiplier]
Image [@Multiplier]*[@[Cost of Goods]]*[@Area]
Flat [@Multiplier]


i tried running VLOOKUP([@Mtype],$R$3:$S$7,2,FALSE), but that only displays the string and not the result of the string. I've also attempted the INDIRECT and CONCATENATE functions after trying to research this solution on google. But I either get the string, or I get a REF display.

I was hoping by using the table format opposed to actual cell references, it would display the correct value depending on the row i was working on. I figure if I put a sample value in for the equation, it would not display correctly.

But if there is a trick for this, my columns are listed as follows. AREA= I, COGS= J, Mtype= K, Multiples= L, and the fields I am trying to calculate is under M. I've got 597 rows currently and will likely add more.

I want to make this work so that I can import the data into ACCESS to better display the data relationally. And I want an easy way to create different price list for different people by using the multipliers found in another table.

Thanks for any help

[Kaper]

It looks silly, but shall work.
As you have only 5 types of formula may be just nested IFs
=if([@Mtype]="Area",[@Multiplier]*[@Area],if([@Mtype]="Price",[@[Cost of Goods]]*[@Multiplier], ...and so on

[Richard Buttrey]

Hi,

A VLOOKUP requires that the value being looked up (Mtype in your case) be in the first column of the lookup table.
You mention a 4 column lookup table. Is the MType value in column R since your lookup is restricted to two columns R&S

Otherwise upload your workbook and manually add some example results that you wish to see indicating with a note which are the result cells and which are the data on which the results have been based

[jcc5018]

the mtype is in the first column of the lookup table. The vlookup function itself performs as expected. My main problem is taking the formula's from the result and making it actually work as a formula opposed to a string.

And yes, a long nested IF does do the job, and if I have to stick with that I suppose that works, but if there are better functions that would accomplish this with less to type, that would be ideal for future reference. (it's manageable now with only 5 equations to deal with, but what if I had something more complex.) In other words, I want to be able to create a way to write the formula I expect a particular cell to perform dependent on a set of factors. So, if i had an IF function, Instead of having to type in the formula I'd want it to perform, I'd have it reference a field that has the particular formula.

I may like to do something similar with a series of discount rules that my table will have.

So, I suppose the underlining question is how do i make a general forumla in such a way that it will reference the appropriate cell no matter where i copy it.

like if i always wanted it to pull the value from the corresponding row of the AREA and multiplier column? But the formula itself may be located in a different worksheet. So when i reference that formula in the price column of lets say row 35, then it will find the values of area and multiplier of row 35. and not of some random row on the other worksheet?

Does that make sense?

In programming terms, I essentially want to create my functions sheet separately, and be able to reference them depending on the determining value of each row.

[Kaper]

Unfortunately, VLOOKUP and other functions return in such cases the string representing formula, not the formula result.
Of course one could use macro to evaluate it. For instance looping through such column and for each cell assign

Please Login or Register  to view this content.

PS. One could also use old Excel4 macro EVALUATE - but to use it successfully nowadays it has to be a part of name (Ctrl+F3) definition. Anyway it is quite "exotic solution".

[jcc5018]

OK I suppose I'll look into how to do vba code later. I've gotta get to sleep now. thanks

[Richard Buttrey]

Hi,

Could you upload the workbook with the details requested in post #3. I don't fully understand your description of the problem, hence the need to SEE the result you expect, but it's possible that an INDIRECT() function could be used.

[jcc5018]

sample.xlsx

Here is a sampling of the data with the stated equations and what I am trying to accomplish.

[jcc5018]

any new ideas?