Help assigning number value to letter based on position within a sequence

[alexkap]

I am using Microsoft Excel 2013. I am hoping for an explanation on how to accomplish the following using Excel...

I have a list of approx. 1000 9-character sequences of letters (ex: GANTQYFGA) in one column (column B).
In the next 9 columns (column C-K), I have each letter from the respective sequence in its own separate cell (ex: c2-G, d2-A, e2-N, etc)
I want to assign a number value (essentially a score) to each letter in the sequence, depending on its position in the sequence. For example, if a "G" is in position 1, it's score would be X. If a "G" is in position 8, it's score would be Y. There are 20 possible letters that can be found in 9 possible positions, for a total of 180 individual score values that are in my "key". To be brief, the values in my "key" are from previous data. For my new data set, I am attempting to score each 9-letter sequence based on values calculated from my previous data (values are based on the frequency at which a certain letter was found at that position in the sequence).

Rather than go through each 9-letter sequence, refer to my key, and manually type in each value (for roughly 1000 sequences, this would require me to manually enter 9000 values!), I am hoping there is a way to input my "key" into excel and have it fill in the values for each position of every sequence.

Can anyone help me figure out how to do this? I have a very basic skill level using Excel. I've never used any advanced programming or macros.

I have attached a copy of my .xls data file for reference. On the right ride (columns AA-AJ) is how I would like the output to look. Ignore columns A, L & M, as well as P1-3 & Q1-3 and any highlighting (irrelevant to this question).

I would really appreciate any assistance!

Thanks,
Alex

[GC Excel]

Hello,

No need of a macro. Try this formula in AA2 and copy to the right and down.

=INDEX(Q$7:Q$26,MATCH(C2,$P$7:$P$26,0))

[alexkap]

This worked perfectly!

Thank you GC Excel!!

[FDibbins]

This sounds do-able. what are the values you need to apply to this?

[alexkap]

Problem solved FDibbins. Thanks anyway!

[GC Excel]

You're welcome and thanks for the rep !