VBA Evaluate Index Match

  • Hi Ozgrid


    I have done a fair bit of searching and can't find a answer for this specific case.


    My data set is ~650k lines long so i have been trying to make the code as quick as possible.
    What i am tring to do is get VBA to index match an entire column with another sheet.


    So fare my code is

    Code
    Sub testevaluate()
    
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("Q2:Q" & LastRow).Value = Evaluate("INDEX(MATCH(C2:C" & LastRow & ",'CC Map'!A:A,0),0)")
    
    
    
    
    End Sub


    This very quickly compares column C to sheet 'CC Map'!A:A; and puts the value in column Q.
    However, I want to return the corresponding value from 'CC Map'!B:B


    When I use


    It return every value the same (corresponding to match C2...)



    I think the issue is with having an array as the second argument in INDEX, but i am trying to avoid loops.
    What i have at the moment runs almost instantly.



    Any help is greatly appreciated.


    Thank you

Participate now!

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