How to convert Index - Match Function to VBA ?

  • Hi, everybody. My demo file used index and match in cell B11
    {=IFERROR(INDEX(Schedule!G9:G100000,MATCH(1,(B9=Schedule!D9:D100000)*(A11=Schedule!B9:B100000),0)),"")}
    How to convert it to VBA ? And how if i want it's auto code for all cell in table ? May i can make it ( Cell B11 ) changed color ( conditional formatting ) based on column H in sheet "Schedule" ?
    Please show me how to do it. Thanks advance.
    [ATTACH]n1223977[/ATTACH]

  • Hello,


    You say your formula is cell B11 ... fine ... but in which sheet ... ???


    In sheet Calendar ...there are 49 Columns and 21 Rows for an Array Formula ... (i.e over 1'000 cells )


    So by all means, if you do not want to kill your workbook performance ...


    your macro should display the final results in your Calendar Sheet ... and NOT the Array formulas ...


    Hope this will help

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

  • Hello,


    To optimize your workbook performance :


    1. The Array Formula should only refer to used rows in sheet Schedule (not 100'000 rows ..)


    2. All cells (i.e. 1'000 cells ) should display the RESULT ...and NOT the FORMULA


    Hope this clariifies

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