how to compare partial match in two columns

dear friends i have a sheet there data in two columns as like in in A
A1 456511360928224677144335
A2 ap04ce3525
A3 AMBO
A4 ap09BC089.3
A5 456508662366576711095117
A6 455992607612506222256460
A7 NEW2013
A8 889042333040775015097234
A9 AP09T4333

and in column B
B1 AP09 CL 6081
B2 AP09 T 4333 PASS
B3 MP 14 AV 9058 -
B4 AP04H4420
B5 AP04TA2769
B6 up04 hc 7656
B7 CuG 04 HC 0276
B8 AP 04 CA 3552 -
B9 AP04TA9042

i need match these two column and display match result in column C as like A9 match with B2 so result AP09T4333 shown in C9
pls help me
thanks in advance

Hi

the attached workbook should get you started.

I used column c to get rid of all the spaces in column B as column A has no spaces

Column d is used to find the row in column A that matches an entry in column B
Column D lists the value in column A.
You should be able to modify this to suit your requirements.

Thx sir for your most valuable help

Originally Posted by mehmetcik

Hi

the attached workbook should get you started.

I used column c to get rid of all the spaces in column B as column A has no spaces

Column d is used to find the row in column A that matches an entry in column B
Column D lists the value in column A.
You should be able to modify this to suit your requirements.

Thx sir for your most valuable help