Using Evaluate to complete a range of cells

[merry_fay]

I have a column of data with about 30,000 rows in it.

I need to do a lookup based on 2 criteria so I've come up with this:

Range("P2").Value = Evaluate("INDEX(Mapping!$C:$C,MATCH($G2 & $J2,Mapping!$A:$A&Mapping!$B:$B,0))")

Great

But now I need to work out how to copy it down to the rest of the rows.

I can do the P2:P -whatever the final row is bit, but I need to make G2 & J2 change according to the row it's on.

Something like:

Range("P2:P"&lst_row).Value = Evaluate("INDEX(Mapping!$C:$C,MATCH($G" & row " & $J" & row",Mapping!$A:$A&Mapping!$B:$B,0))")

I don't want to do a loop as it would take too long with the volume of data, & there's already huge amounts going on with the code.


Thanks

 

[RoryA]

It works fine for me with data in both columns - you would get #N/A if there isn't a match on both columns though.

It is generally better to limit formulas to just the cells of interest - especially if they are array formulas - though some formulas (eg COUNTIF / SUMIF) are well enough optimised that it doesn't really seem to matter.

 

[sheetspread]

I always thought it was best practice to reference a whole column

I do it too sometimes, but it's not best practice.

Here are some good links to read about the scripting dictionary:

http://www.mrexcel.com/forum/excel-questions/660596-scripting-dictionary-exits-keys-items-etc.html (part of it is pasted from another page)

http://www.mrexcel.com/forum/excel-...p-visual-basic-applications-code-vlookup.html A good example here

random googling is good too so you can just read what interests you at first and not be overwhelmed by theory.

PowerPivot is another option worth thinking about

 

[merry_fay]

You're right, it does work -I just limited my columns a bit too much, d'oh -it was bizarre coincidence that meant the ones falling in the limited range only had one value to lookup on, & the one's with 2 weren't in the lookup or fell out the range...
It runs really quickly too!


Are formulae such as lookup's, Offset, Match, Index well enough optimised too? Is there somewhere I can find out?

Thanks again!!

 

[merry_fay]

Thanks sheetspread too -I've bookmarked it so I can have a look when I've got a bit more time.

 

[RoryA]

I don't know if there's a reference anywhere. INDEX is generally pretty efficient but I'm not sure about MATCH or VLOOKUP. OFFSET wouldn't typically be considered optimised to my mind as it's volatile, though I'd rarely pass an entire column to it.