Text&number based column to auto display new text in new column

Please help and assist with my problem. I am having a hard time creating a formula and function on "microsoft exel 2007". I have one column containing a text & numeric value, and what I want is for these values to be matched, replaced, and auto inserted into another column, containing a predisposed text and numeric value. Is this possible? What formula and nested functions do I need? Do I need to set up a table to draw the information from. I am able to give more specific information pertaining to problem. Thanks fellow users.

Here is some of the data where my problem lies. I would like for column "A" to auto enter column "B" data.

There are multiple values that correspond with same desired outcome values. Examples: DGM66,DGM67,DGM62. These values,in column A I would like to be represented in the adjacent column, column B with their matched type, "4L60". Column A has multiple matches, represented by one value in column B. Is this possible to set up in this fashion?

X1	Y
X2	Y
X3	Y
A1	B
A2	B
A3	B

What I'm trying to get at is, I'm trying to work a1, a2, and a3 in column A into being recognized as B in column B. However, at the same time differentiating X1, X2, and X3 as Y and adding Y and B into column B automatically. How can I do this?

Something like this?

Thank you for your time. This does look promising however, I am trying to get the value into auto entered into column D. I do suppose my question is semi unclear. Would you mind if possible reworking the equation into column D for me as I study your work.
Thanks a bunch.

Column a and b are actually column c and d. Sorry for the confusion.

Originally Posted by TCF

X1	Y
X2	Y
X3	Y
A1	B
A2	B
A3	B

What I'm trying to get at is, I'm trying to work A1, A2, and A3 in column A into being recognized as B in column B. However, at the same time differentiating X1, X2, and X3 as Y and adding Y and B into column B automatically. How can I do this?

if it helps Y and B in column B are meant to be what X1, X2, X3; and A1, A2, A3 in column A are to be identified as. As in X1 is in the Y family, and A1 is in the B family. Should I create a separate page (page2) with all the X's and A's and what family each respective one belongs to and form a script to force (page1) to draw from that? is that possible? How could I do that if it is?