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

    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!