Linking a Number Code to a Name

I am looking to change a number code to a name.

For example, Column D, Rows 2 - 500 contain different number codes:

• 451
• 461
• 593
• 675

I want to match up the number code and replace it with the Course Name.

• 451 = Course A
• 461 = Course B
• 593=Course C
• 675=Course D

I am looking to have the number code, i.e., 451 replaced in Column D with the Course Name, Course A or the Course Name appear in Column E pulling from the number code.

In the end I will probably have 100 unique number codes to match up with course names.

Any suggestions on how do this would be greatly appreciated.

Thanks!

Develop a table that compares number codes to courses. Then on a separate sheet, you can use a vlookup formula to link them.

If you post a sample file, we can help you better.

Thanks for your reply!

Here is my sample spreadsheet.

Try this and let me know if it works:

Enter this formula into cell E1, and fill down as far as necessary

=IF(ISERROR(VLOOKUP(D1,$H$2:$I$20,2,FALSE)),"No Match",VLOOKUP(D1,$H$2:$I$20,2,FALSE))

This formula will return "No Match" if a course code is listed that is not in your lookup table.

It works perfectly! I can't thank you enough!

One more question though. If I wanted to add additional courses to the table, how would I go about doing this? Also, if I wanted to move the table of courses to a new spreadsheet, how would the formula change?

Thank you! Thank you! Thank you!

Ill break down the VLOOKUP formula for you:

=VLOOKUP(LOOKUP_VALUE,LOOKUP_RANGE,COLUMN,FALSE)

LOOKUP_VALUE: This is the number that you are looking up. In your case, it is the number in column D. So, if you are enterring the formula in E1, your lookup value is D1.

LOOKUP_RANGE: This is the table that you want to search to find the lookup value. You can just highlight the range while entering the formula. For example, after typing the lookup value, then comma, you can just go and highlight the range that you want to lookup. NOTE: it is important to absolute the range (use $ signs). This prevents the range from changing as you fill your formula down. For example, if your lookup range is A1:B10, and you fill that down, it would change to A2:B11, unless you set it up as $A$1:$B$10.

COLUMN: this is just the column that you want to pull the info from. In this case, you want to return the course name, which happens to be the second column in your range, so you use the number 2.

TRUE/FALSE: using false just tells excel that it has to be an exact match.

Let me know if you have any further questions, or need any additional help.

I'm using the vlookup to search data in one columm (D =number), match data from the table (H & I) and return a name (E) that cooresponds with the number. This fomula works perfectly....
=IF(ISERROR(VLOOKUP(D1,$H$2:$I$20,2,FALSE)),"No Match",VLOOKUP(D1,$H$2:$I$20,2,FALSE))

Now I want to count the number of entires for each returned name and return a number in column J. I have attached to worksheet.

If I understood your request correctly, enter this formula in cell J3 and fill down:

=COUNTIF($D$2:$D$88,$H3)

Let me know if that works.

It works...Thank you so much!

Glad I could help out.

HI Bigbas
I also want to link my table

i mean
when i type the staff name.the account number is automatic fill in the "account number" row

here's my file.
please Bigbas help me to link this.

sorry for my broken English.