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!

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,R OWS($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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Prelate" wrote:
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!