VBA code for Two dimensional lookup (Rows & columns) with multiple criteria's.

  • So ... to be concrete ...


    Is the next step to deal with Columns AQ to AY in worksheet Display ... and incorporate your 9 Formulas into your existing macro ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • OK ... understand the priority ...


    Regarding the macro speed and performance ... you have decided to remove :

    Code
    'Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual

    which were initially added ... exactly for your objective : improving speed and performance ... !!!:S

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Attached is your test file version 7 ...


    All 9 Formulas have been incorporated into your macro ...


    Please note since you have decided to remove all data, there is no means to check the results produced by these formulas ...


    Hope this will help

    :)

  • Really noob in coding :D;(...........tried to modify.... not producing output...any help please?




    All formulas in Display Sheet AQ:AY....edited two references to make the formula work dynamically


    From

    INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Summary!$C$8&Summary!$I$8&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))


    Changed to

    INDEX(JCB!$C$6:$CB$500,MATCH(Display!D9,JCB!$B$6:$B$500,0),MATCH(Display!$I$3&Display!$AG$4&Summary!$I$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))



    Code
    these references need to be made as As long (for all formula)
    c = Evaluate("=INDEX(JCB!C6:CB500,MATCH(Display!D" & i & ",JCB!B6:B500,0),MATCH(Display!I3&Display!AJ4&Summary!K6,INDEX(JCB!C1:CB1&JCB!C2:CB2&JCB!C3:CB3,0),0))")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!