VBA: Index match multiple criteria & autofill until last row with Column as ref

  • Hello Excel Masters,


    Firstly I apologize if I am asking too many requirements in this thread for a single operation.


    Please find attached document having the requirements indicated below:
    1) There are two spreadsheets, Sheet1 having inputs while Sheet2 having outputs.
    2) Sheet2 has Index match multiple criteria with excel formula until the last row of the reference column.
    3) Spreadsheet name & columns are dynamic in nature.


    The number of rows are exceeding more than 20,000+, hence it is becoming cumbersome to use excel formula leading to high file size and as well as Excel document is hanging by consuming too much of time.


    Henceforth it's my request to help me in performing this operation via VBA. I hope it is feasible.


    Thanks for your time & support in advance.


    - Billy

  • Hello,


    Once you have tested out the macro ... feel free to share your comments ...

    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:)

  • Hello,


    Once you have tested out the macro ... feel free to share your comments ...


    Hi Carim,


    Many thanks for the solution. Sorry I was able to have a look now only.


    I will try the code & provide the feedback to you shortly.


    - Billy

  • Hi Carim,


    I tried to map the code to my actual requirements but the code is not working to my bad luck.


    I am still a rookie to VBA & thus I failed miserably.


    I have attached updated file with actual requirements (Columns have been moved in both Sheet1 & Sheet2 as per my requirements). I once again kindly request you to provide the solution.


    Further in Sheet2 in Column A the operation in identical but limited to Column A only along with above part.


    Thanks for your kind support.


    - Billy

  • Hi,


    First question would be about your Test file ...


    Is the macro producing the expected results ... or not ... ???


    Quite important ... since you are dealing with a number of rows in excess of 20'000 ... !!!!!!

    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:)


  • Yes Carim, the test file is meeting all the requirements defined & indeed is delivering desired results.


    But I failed it mapping to actual requirements as defined above.


    Its my sincere request for you to help me.


    Again, thanks for your response & support in advance.


    - Billy



  • Glad to hear the macro does what you were expecting :wink:


    Thanks for .. both your Thanks ..AND for the Like :smile:

    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:)

  • Glad to hear the macro does what you were expecting :wink:


    Thanks for .. both your Thanks ..AND for the Like :smile:



    Hi Carim,


    I am sorry to bug you over & over.


    I have one last query, what will be the change in the code if I am changing the spreadsheets names i.e. Sheet1 as Actual, Sheet2 as PT, Sheet3 as Planned.


    As the VBA is not functioning if I am changing the spreadsheets names.


    Thanks for your kind support again.


    - Billy

  • Hello,


    Your macro is using Sheets Code Names ... so you do have to worry about changing the Sheets Tab Names ... :wink:


    To keep things simple ... there is one exception within the Evaluate function ... see attached Version 3


    Hope this will help


  • Thank you, thanks a lot for your support.


    Its embarrassing for me to ask you so many queries. Since I am a noob in Macro, I am not able to crack the code efficiently.


    The last code going by:


    col = 1
    Set rng = Sheet2.Range(Cells(2, 1), Cells(last2, 1))
    For Each c In rng
    i = c.Row
    c = Evaluate("=INDEX(Actual!" & Col_Letter(col + 8) & "1:" & Col_Letter(col + 8) & last1 & ",MATCH(J" & i & "&K" & i & ",Actual!B1:B" & last1 & "&Actual!C1:C" & last1 & ",0))")
    c = IIf(IsError(c), "", c)
    Next c



    If I need to change the column number then I tried col = 2 but I am not able to fetch the results in Column 2 instead the results are appearing in Column 1 only. I am really sorry to pester you, this shall be the last query as all of my requirements has been cracked by you.


    Once again I thank you very much from bottom of my heart for helping me out.


    - Billy

  • A


    And the code works absolutely!!!!!


  • Hi Carim,


    I cracked the code to modify as per my requirements.


    Thank you again.


    - Billy

  • Glad to hear you have managed to adapt the macro to your real-life file ...:smile:

    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:)

Participate now!

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