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

Sub testevaluate()




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




End Sub

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

Re: VBA Evaluate Index Match

I cross posted this over at stackoverflow and got a great response

http://stackoverflow.com/quest…/vba-evaluate-index-match