vba multiple index match through rows and columns

  • Dear vba masters,

    I'm requesting some help transforming my index multiple match excel formula into a vba procedure.

    I have been able to start with a vlookup but as i have to loop through rows and columns it won't be sufficient.

    In the calcul sheet, i need to find the result in the sheet resultats corresponding to the col C3 and the row 2, then with that result find the corresponding model that is in the row 1 between the sheets calcul and prime par modele.

    This will give an amount that i want to multiply by the number of models found in the sheet data.

    Below my code so far but i ended with a double vlookup and only able to loop in the first column, i need to do it for all columns until "HT"

    and also the excel formula that gets me the result i need in vba

    Excel formula

    =INDEX('Prime par modèle'!$G$2:$BA$26;MATCH(INDEX(Resultats!$A$1:$GB$99999;MATCH(Calcul!$C3;Resultats!$C$1:$C$99999;0);MATCH(F$2;Resultats!$A$1:$GB$1;0));'Prime par modèle'!$F$2:$F$31;0);MATCH(Calcul!F$1;'Prime par modèle'!$G$1:$BA$1;0))*COUNTIFS(data!$I$2:$I$100000;F$1;data!$L$2:$L$100000;$D3;data!$AU$2:$AU$100000;$C3;data!$AF$2:$AF$100000;0))

    VBA code

    Many thanks in advance for any help provided !!

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • Hello Roy,

    Thanks for your advice, i will be carefull with the codes lines i post in the future.

    I wanted to find a vba solution to avoid haing hundreds of formulas that lower the speed of my file.

    I would like also to give that file to other users so they just have to push the button to make the calculations

    Don't you think it would be better ?

  • No, I don't.

    You don't need to add multiple calculations if you use Excel's Table feature. A Table can have calculated Fields so that Formulas copy down as rows are inserted

    Overview of Excel tables

  • I'm not a big fan of tables in excel, i think we loose flexibility with it..

    But i will give a try

    now i'm still looking for a vba solution because i'm stuck on this for 2 days already and i would like to see the correct way to do it so if you have any advice on the vba code :)

  • because of the way the formulas are spelled, i don't like it

    and i'm not familiar with tables as i said but i will give it a try !