Try this code. You will get 0 if there arre no matches, otherwise, the
result will be the number of matches

=Sumproduct(--(left(Sheet1!A1:A100,3)&Right(Sheet1!A1:A100,2)=le ft(A1,3)&Right(A1,2)))

" wrote:

Hi,

I have two sets of data, data in sheet 1 column a has about 400 rows
in each row there is data with wild cards, sheet 2 have about 4000
rows and has the data without the wild cards, i want to be able to
what data in sheet2 appears in sheet 1, cannot seem to get this to
work, example data shown below

Sheet 1 Sheet 2

APAXX12 APADR12
APAXX34 APATY89
APRXX89 APRJNDT
APRXXDT

x Is the wild card in sheet1

Please Help i am going crazy!!