Assigning words to a number

[Prelate]

I am trying to build a formula, where I can have assign a number to a phrase.
Specifically, I want to make a list of training classes people have attended,
and I want to enter a number in the cell, and have it grab the class name.

Conversely, I want to use the same cells, to auto fill another field, that
any numbers NOT in the first cell, will be auto-filled in the other field, to
indicate the name of classes NOT taken.

all help is much appreciated!

 

[Max]

Try this sample which illustrates one way to get both result sets,
ie the classes attended list, & the converse result, classes not attended

http://www.freefilehosting.net/download/3d07d
Extract classes attended n converse listing.xls

Source reference table created in cols A & B,
Class descriptions in A2 down, reference codes in B2 down

Assume input codes for classes attended will be entered in E2 down
In F2:
=IF(E2="","",INDEX(A:A,MATCH(E2,B:B,0)))

In G2:
=IF(B2="","",IF(ISNUMBER(MATCH(B2,E:E,0)),"",ROW()))
Leave G1 blank

In H2:
=IF(ROWS($1:1)>COUNT(G:G),"",INDEX(A:A,SMALL(G:G,ROWS($1:1))))
Select F2:H2, copy down to the last row of source data in col A.
Minimize/hide col G. Col F will return "Classes Attended" while col H returns
the converse: "Classes NOT attended"