[Tonservices]

For the number date do I change both 8's in this formula? IF(ISNA(INDEX(Sheet2!A:I,MATCH(A2,Sheet2!A:A,0),8)),"MISSING",INDEX(Sheet2 !A:I,MATCH(A2,Sheet2!A:A,0),8)

[slurpee55]

You don't have to concatenate (the A&B&C-type formula) in column A, you can put it in any column - let's say M, and change the formula to
=IF(ISNA(INDEX(Sheet2!A:I,MATCH(M2,Sheet2!A:A,0),8)),"MISSING",INDEX(Sheet2 !A:I,MATCH(M2,Sheet2!A:A,0),8))
If you want to put the combined data in column M in each sheet, it would be
=IF(ISNA(INDEX(Sheet2!B:M,MATCH(M2,Sheet2!M:M,0),12)),"MISSING",INDEX(Sheet 2 !B:M,MATCH(M2,Sheet2!M:M,0),12))

[Tonservices]

Is this correct, I pasted (B2&C2&G2) in sheet 1 column M. I pasted (B2&D2&G2) in sheet 2 column m. I pasted IF(ISNA(INDEX(Sheet2!B:M,MATCH(M3,Sheet2!M:M,0),7)),"MISSING",INDEX(Sheet2! B:M,MATCH(M3,Sheet2!M:M,0),7)) in sheet 1 column J ( For"Number"). I pasted IF(ISNA(INDEX(Sheet2!B:M,MATCH(M3,Sheet2!M:M,0),8)),"MISSING",INDEX(Sheet2! B:M,MATCH(M3,Sheet2!M:M,0),8)) in column K (For "Nunber date"). Please correct me if I doing something wrong. Thank

[slurpee55]

That is correct and it works for me - I get no "Missing" responses, and when I compared the original data with the results using another method they were all correct.