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

1. ## 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

2. ## Re: How to use a substitute formula to substitute text entries to a different text en

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

3. ## Re: How to use a substitute formula to substitute text entries to a different text en

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

4. ## Re: How to use a substitute formula to substitute text entries to a different text en

Use absolute reference to table.

=VLOOKUP(A2,\$G\$2:\$H\$11,2,FALSE)

5. ## Re: How to use a substitute formula to substitute text entries to a different text en

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

Andy

6. ## Re: How to use a substitute formula to substitute text entries to a different text en

Why the objection to using a table?

=VLOOKUP(A2,{"w","2b";"1c","3c";"1b","3b";"1a","3a";"2c","4c";"2b","4b";"2a","4a";"3c","5c";"3b","5b";"3a","5a"},2,FALSE)

7. ## Re: How to use a substitute formula to substitute text entries to a different text en

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?

8. ## Re: How to use a substitute formula to substitute text entries to a different text en

No problem other than maintaining them.

A single table is a lot easier to change rather than all the cells that use the same static table in formula.

they have told me to use ones without tables
Who is they and in what context. Do you have links to this other posts?

9. ## Re: How to use a substitute formula to substitute text entries to a different text en

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

10. ## Re: How to use a substitute formula to substitute text entries to a different text en

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1