excel - Comparing two data sets for matches and nonmatches -

i have data in 2 different sheets. sheet1.a contain alphanumeric entry "abc123" , sheet2.a contain similar entry "abc123 text" or "some text abc123"

additionally sheet1 have less entries sheet2, therefore there nonmatches.

in sheet3 want able display entries sheet1.a corresponding match sheet2.a , non matches, them displayed @ bottom of list.

example of ideal output:

sheet3.a  sheet3.b abc123    abc123 abc222    abc222 abc333    abc333           abc444           abc555           abc666 

currently using index match (with left function) formula sheet3.b not produce ideal output:

sheet3.a  sheet3.b abc123    abc123 abc222    abc222 abc333    abc333           abc444           abc444           abc444 

also because using left function , data in sheet2.a may not arranged similar sheet1.a, entries not being found, producing #n/a

i add sheet2.a may contain more 256 characters causing problems index match function. issue not top priority if can resolved well, great.

edit:

question , accepted answer reflect 1 another

you use .find method, searching partial matches.

sub findpartialstring()  dim wslist worksheet dim wssearch worksheet dim wsoutput worksheet dim lastrow long dim rnglist range dim rngmatch range dim cl range dim arrnonmatches() variant dim nonmatchcount long   set wslist = sheets(1) '## modify needed set wssearch = sheets(2) '## modify needed set wsoutput = sheets(3) '## modify needed set rnglist = wslist.range("a2:a5") '## modify needed  each cl in rnglist     set rngmatch = nothing 'clear container before each query     'look partial match:     set rngmatch = wssearch.cells.find(what:=cl.value, after:=activecell, lookin:=xlformulas, lookat _         :=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, matchcase:= _         false, searchformat:=false)      'store matches , non matches in separate arrays:     if not rngmatch nothing         lastrow = 1 + application.worksheetfunction.counta(wsoutput.range("a:a"))         'put searched value in column a:         wsoutput.cells(lastrow, 1) = cl.value         'put found value in column b:         wsoutput.cells(lastrow, 2) = rngmatch.value     else:         'store non-matches in array         redim preserve arrnonmatches(nonmatchcount)         arrnonmatches(nonmatchcount) = cl.value         nonmatchcount = nonmatchcount + 1     end if next  'print out non-matches lastrow = lastrow + 1 wsoutput.cells(lastrow, 1).resize(ubound(arrnonmatches) + 1, 1).value = application.transpose(arrnonmatches) end sub