How to use a substitute formula to substitute text entries to a different text entry

Hi Guys

I am a teacher and am trying to get excel to substituite childrens grades at year 2 to what they should get at year 6, for example, a child getting a 2b at year 2 should get a 4b at year 6.

How can i write a formula that will change childrens levels in one cell to predicted levels in another cell. (conversion rules below)

w = 2b
1c = 3c
1b = 3b
1a = 3a
2c = 4c
2b = 4b
2a = 4a
3c = 5c
3b = 5b
3a = 5a

Much appreciated help

Andy

Originally Posted by Andy Pope

You can use a VLOOKUP function to return Year6 grade based on Year2's value.

=VLOOKUP(A2,G2:H11,2,FALSE)

Assumes Year2 grade in A2
Lookup table in G2:H11

Thanks for your help, much appreciated, however when i drag that formula down for other cells to use, it also changes lookup table cells in the formula, anyone round that?

Andy

Is there any other way of doing it without creating a table?

Andy

Dont know really Im new to all these formulas and have been creating some really complicated ones using you guys to help and they have told me to use ones without tables so want to stick to those and try and get my head round them. Is there a problem with doing it that way?

No its just when i asked for suggestions someone suggested a code which worked without a table so i have just stuck to that method. It works fine though in this instance becuase the codes will never change.

You guys are such a help thanks

Andy