Optimize Excel Formula that uses large arrays -

i have used below mentioned excel formula.

=index(tabl,small(if(countif(h2,$a$1:$a$325779)*countif(i2,"<="&$b$1:$b$325779),row(tabl)-min(row(tabl))+1),1),3) 

where "tabl",a table, a1:e325779 , source of lookup array.

the formula mentioned exact requirement taking lot of time update excel 400,000+ cells containing formula.

can optimized? or can equated faster macro?

its taking 1 second update 1 cell!!! that's long time update 400k+ cells once!!!

screenshot of sample worksheet below.

i have based program on martin carlsson's. processing 100 records in 30 seconds. can improved?

sub subfindvalue()     application.screenupdating = false     application.displaystatusbar = false     application.calculation = xlcalculationmanual     application.enableevents = false      cells(2, 12) = format(datetime.now, "yyyy-mm-dd hh:mm:ss")      dim varrow variant     dim varrowmain variant     dim lookuptable variant     dim lookupvaluetable variant      lookupvaluetable = range("g2:j309011").value     lookuptable = range("a2:d325779").value      varrowmain = 1     varrow = 1      until varrowmain = 309011         until varrow = 325779             if lookuptable(varrow, 1) = lookupvaluetable(varrowmain, 1) , lookuptable(varrow, 2) >= lookupvaluetable(varrowmain, 2)                 lookupvaluetable(varrowmain, 3) = lookuptable(varrow, 3)                 lookupvaluetable(varrowmain, 4) = lookuptable(varrow, 4)                 exit             end if             varrow = varrow + 1         loop          if isempty(lookupvaluetable(varrowmain, 3))             lookupvaluetable(varrowmain, 3) = "na_ox"             lookupvaluetable(varrowmain, 4) = "na_oy"         end if          varrowmain = varrowmain + 1         varrow = 1     loop     range("g2:j309011").value = lookupvaluetable      cells(3, 12) = format(datetime.now, "yyyy-mm-dd hh:mm:ss")      application.screenupdating = true     application.displaystatusbar = true     application.calculation = xlcalculationautomatic     application.enableevents = true end sub 

this should work , faster vba solution require looping every row long can sort date in column b descending:

enter following formula array (instead of enter use ctrl+shift+enter

=index($c$1:$c$15,match(g2,if($a$1:$a$15=f2,$b$1:$b$15),-1)) 

you should end like:

explanation:

if($a$1:$a$15=f2,$b$1:$b$15) 

is building array of values equal rows in column b test word in same row column a.

match(g2,if($a$1:$a$15=f2,$b$1:$b$15),-1) 

this using array built id statement find smallest value greater or equal value test data.

=index($c$1:$c$15,match(g2,if($a$1:$a$15=f2,$b$1:$b$15),-1)) 

once 'index' return value in column c @ same position matched value.

update: if looking tigeravatar's answer returns here vba function return values:

sub getvalues()  application     .screenupdating = false     .enableevents = false     .calculation = xlcalculationmanual end  dim strmetalname string: strmetalname = [e3] dim dbminimumvalue double: dbminimumvalue = [f3]  range("g3:g" & rows.count).clearcontents  range("tabl")     .autofilter field:=1, criteria1:=strmetalname     .autofilter field:=2, criteria1:=">=" & dbminimumvalue, operator:=xland      range("c2", [c2].end(xldown)).copy [g3]     .autofilter end  application     .screenupdating = true     .enableevents = true     .calculation = xlcalculationautomatic end end sub 

for me took 5-7 minutes run while took 1.5 seconds, first answer returns single row containing closest matching result sub return values greater or equal too.