ExcelBanter - Assigning words to a number

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!