Converting a 6 letter column to a 2 letter column using Vlookup. How do I do this?

[Jwan622]

I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX R$
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....


The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?

 

[nuked]

=MID(A1,3,2) fill down

 

[Jwan622]

but the two letters I'm extracting are different than the letters in the middle of that column

 

[nuked]

Where do the letters come from then?

 

[Jwan622]

They just simply correspond to the 6 letter chain of words. I'm not extracting the letters from the 6 letter chain, they just correspond to the 6 letter code... and I need to fill in the rest of the 2 letter column. Is there any way to convert all of the 6 letter codes to 2 letter codes?

 

[nuked]

They just simply correspond to the 6 letter chain of words. I'm not extracting the letters from the 6 letter chain, they just correspond to the 6 letter code... and I need to fill in the rest of the 2 letter column. Is there any way to convert all of the 6 letter codes to 2 letter codes?

Please resolve these near-consecutive contradictions, and explain the logic behind how the 2-letter codes are derived.

 

[Jwan622]

The two letters just correspond to the 6 letter code, but we are not extracting them from the 6 letter code. I've already listed 100% of the corresponding relationships... I just don't want to have to type them by hand.

 

[nuked]

Line 1: XXHSXX = HR Line 2: XXHSXX = HS