Actualization with Vlookup in Excel-CodePudding

I have two tables, table2 in Sheet2 and table1 in Sheet1. I need to calculate the "vf" column in table1. All values are random, except for vf which is a function of all other values (Formula below). The actual formula works fine but I have to enlarge the table2 till column 100 (in this example with other randomic values). The problem is: how can I rewrite the formula for "vf" in order to be shorter and understandable when I will have to add 100 columns to the table2?

The Formula to create "vf":

=IF(AND(A2="s2",E2>1),(C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,2,FALSE) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,3,FALSE)*(1/(1 F2)^2)*(IF(E2>=2,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,4,FALSE)*(1/(1 F2)^3)*(IF(E2>=3,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,5,FALSE)*(1/(1 F2)^4)*(IF(E2>=4,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,6,FALSE)*(1/(1 F2)^5)*(IF(E2>=5,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,7,FALSE)*(1/(1 F2)^6)*(IF(E2>=6,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,8,FALSE)*(1/(1 F2)^7)*(IF(E2>=7,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,9,FALSE)*(1/(1 F2)^8)*(IF(E2>=8,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,10,FALSE)*(1/(1 F2)^9)*(IF(E2>=9,1,0)) C2*D2*VLOOKUP(G2,Sheet2!$A$2:$K$7,11,FALSE)*(1/(1 F2)^10)*(IF(E2>=10,1,0))),C2*D2*H2*VLOOKUP(G2,Sheet2!$A$2:$K$7,2,FALSE))

Table1 in Sheet1:

Table2 in Sheet2:

Sort of pseudo code (c style) to translate in VBA:

double VF(std::string c1, int year, double v1, double v2, double v3, double v4, double v5) {

    double vf = 0; //initialize vf
    int initialtableLookUpColumnNo = 2;

    if ((c1 == "s2") && (v2 > 1)) {   
        for (int i=initialtableLookUpColumnNo; i=v2 1; i  ) {
            vf  = vlookup(v4, Sheet2!$A$2:$K$7, i, FALSE) * v0 * v1 * (1/(1 v3)**(i-1));
        }      
    }
    else {
        vf  = vlookup(v4, Sheet2!$A$2:$K$7, 2, FALSE) * v0 * v1 * v5;
    }
}

CodePudding user response：

Ok, the best solution here is to make an UDF function with VBA. I'll post the steps and code to be exhaustive for someone like me who doesn't know VBA.

Click Alt 11 to enter Visual Basic for Applications
Click Insert Module
Write the following code:

Note that the order of the parameters in the function is at your discretion and you can also specify the type of the parameter such as: c1 as String, v2 as Integer, v5 as Double.

CodePudding user response：

First, you need to remove vlookups from the formula. You can place them in a new seperate column. Then rewrite your formula by replacing vlookups with references to new column.

After this, convert your new formula into a built-in function.

You can name this function as Vf or something you like. And in excel sheet delete your long formula and type your new function with its parameters like: =Vf(A2,C2,D2,G2,F2)