Vlookup but a little more complicated

[dinker454]

Hi all,

I am stuck on how I can manipulate the data I have into what I want. In Column A, I have ID numbers for each product. In Column B-H, I have positions 1 through 7. The last digit of each Column A ID corresponds with the position number in the same row. What I want is to match the position with the ID and have the values for that position to match with the ID. I know this is confusing, so I attached a worksheet. Any and all help is greatly appreciated.

Currently looks like:
ID	Position 1	Position 2	Position 3	Position 4	Position 5	Position 6	Position 7
16236	0	117	150	120	152	121	0		Read right one number in Column A. That number corresponds with position number in the same row, so A4 = 6 and thus should have position 6's value of 121
16234	0	117	150	120	152	121	0
16232	0	117	150	120	152	121	0
16226	0	117	150	120	152	121	0
16224	0	117	150	120	152	121	0
16222	0	117	150	120	152	121	0
16281	122	118	0	112	0	117	123
16282	122	118	0	112	0	117	123
16294	122	118	0	112	0	117	123
16276	122	118	0	112	0	117	123
16274	122	118	0	112	0	117	123
16282	122	118	0	112	0	117	123
16236	122	118	0	112	0	117	123
16237	122	118	0	112	0	117	123
16271	122	118	0	112	0	117	123
Want to look like
ID	Position
16236	121
16234	120
16232	117
16226	121
16224	120
16222	117
16281	122
16282	118
16294	112
16276	117
16274	112
16282	118
16236	117
16237	123
16271	122

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>

 

[Jonmo1]

Try

=VLOOKUP(M2,A:H,RIGHT(M2,1)+1,0)


M2 = the ID to lookup.

 

[Aladin Akyurek]

E2: 16236

and so on...

F2, copied down:

=INDEX(B:H,MATCH($E2,A:A,0),RIGHT($E2)+0)