Excel VBA speed up Vlookup with 2 conditions over a large range -

i have sample of vba lookup concatenation of 2 columns. looksup database feed, row count between 35k , 250k.

doing vlookups way slow times 60 500 seconds. efficient way the same result.

sequence

• turns of screen updating
• turns off calculations
• disables database
• clears clipboard cache
• refreshes db data
• sets lookups
• turns on calculations
• turns off calculations
• copy , pastes values of vlookups.
• enables database
• turns on

s

 sub startcom()   dim ii long, lastrow long   dim starttime double   dim secondselapsed double  ' starts timer      starttime = timer   'freeze screens, clears cache , stops cals     stopall  'set error traps , start , end times      on error goto errortrap:   set sht1 = wsrag set sht2 = wscomdata  sht2.select     reflist  'find last row (in column a) data. , set start row data copy    lastrow = sht1.range("a:a").find("*", searchdirection:=xlprevious).row ii = 9  'disables db connection   wsconfig.cells(7, 2) = 0  sht1.select   range("am" & ii & ":am" & lastrow).formula = "=if(vlookup(concatenate(a"& ii &",b" & ii &"),comment_data!a:f,4,0)="""","""",vlookup(concatenate(a" & ii   & ",b" & ii & "),comment_data!a:f,4,0))" ' comments     calcon     calcoff     range("am" & ii & ":am" & lastrow & "").select         selection.copy             selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _             :=false, transpose:=false  'enable db connection     wsconfig.cells(7, 2) = 1 'determine how many seconds code took run   secondselapsed = round(timer - starttime, 2)  'get lenghth of runtime debug.print "ran in " & secondselapsed & " seconds",    vbinformation startall   exit sub    errortrap:      errormess  debug.print "location: comments start" end sub 

the issue

as understand issue lies vlookup operations, bit here (spread on couple of rows make more readable):

range("am" & ii & ":am" & lastrow).formula =     "=if(          vlookup(concatenate(a"& ii &",b" & ii &"),comment_data!a:f,4,0)="""",         """",         vlookup(concatenate(a" & ii   & ",b" & ii & "),comment_data!a:f,4,0)     )" ' comments 

solution 1

2 solutions suggested in comments:

1. binary vlookup - see here
2. reducing 1 of vlookups

these optimize formulas if want query run in couple of seconds max use ms query...

solution 2 (the fastest - couple sec)

use sql in ms query:

select com.f [currentsheet$] curr  left join [comment_data$] com  on (curr.a + curr.b) = com.a 

this how works. below created 2 example tables.

worksheet name: currentsheet

worksheet name: comment_data

the f column in currentsheet ms query (appended original table). need refresh query using vba or right-click , hit refresh.

how create ms query in excel?

two ways:

1. go data->from other sources->from microsoft query
2. download sql addin (free , open sources) here , input output range of query (f1) , input sql , hit ok