vba - Fastest way to determine what value of a list appears first in a string -

i have list of strings in column on sheet (let "a"), this:

     bjs-lax-gru can-ord-mia-bog nrt-lax-jfk-lim 

and have different list on different sheet (let "b"), this:

 lax mex mia jfk 

so want know value of second list appears first in each string of first list, , need write value next string. in example, get:

                  b bjs-lax-gru        lax can-ord-mia-bog    mia nrt-lax-jfk-lim    lax 

i wrote following code, works perfectly:

dim aux integer dim cur string  j = 1 sheets("a").cells(rows.count, "a").end(xlup).row     aux = 100     cur = ""     k = 1 sheets("b").cells(rows.count, "a").end(xlup).row         if instr(sheets("a").cells(j, 1).value, sheets("b").cells(k, 1).value) < aux , instr(sheets("a").cells(j, 1).value, sheets("b").cells(k, 1).value) <> 0             cur = sheets("b").cells(k, 1).value             aux = instr(sheets("a").cells(j, 1).value, cur)         end if     next k     sheets("a").cells(j, 2) = cur next j 

the problem aware code extremely inefficient, since lists have 200k+ rows. there more efficient way this?

by putting values in arrays , using application.match find match should pretty quick:

sub findmatch()  dim inarr() variant dim oarr() variant dim long dim str() string dim j integer dim ws worksheet dim tws worksheet dim rng range  set tws = sheets("sheet2") 'this sheet lookup list located set ws = sheets("sheet1") ' list of names set rng = tws.range("a1", tws.cells(tws.rows.count, 1).end(xlup)) inarr = ws.range("a1", ws.cells(ws.rows.count, 1).end(xlup)).value redim oarr(1 ubound(inarr, 1), 1 1)  = 1 ubound(inarr, 1)     str = split(inarr(i, 1), "-")     j = 0 ubound(str)         dim fnd         fnd = application.match(str(j), rng, 0)         if not iserror(fnd)             oarr(i, 1) = rng(fnd)             exit         end if         oarr(i, 1) = "none"     next j next  ws.range("b1").resize(ubound(oarr, 1)).value = oarr  end sub