IF Formula

[Guest]

Challenge: I have a column full of sales peoples names. I would like to
write a formula that will identify the persons name in the column and in the
same row, but a different column enter a letter that correspondes to what
type of sales person they are in our organization.

Example: John Smith is listed in the column and he is a National Account
Manager so I would like a formula that recognizes his name and puts "NAM" in
a free cell in his row of information.

Does Excel 2002 have a function that can do this?

Thanks very much.
rich

 

[Guest]

The *most* correct way would be to drive the data from the EmployeeID (you'll
see why). But for what you asked for:

On a separate sheet, build a 2-col table containing names and types:
Name Type
Coderre,Ron MGR
Rosier,Rich OP
etc

Make sure you have an enforced convention for names (Example: Lname,Fname Mi)

Select that range and define a name for it:
Insert>Name>Define
Names In Workbook: LU_EmpName
Refers to: (your range)

Then, on the data sheet, if a name is in A2, then:
B2: =VLOOKUP(A2,LU_EmpName,2,0)

That will find the name from A2 in the 1st column of the LU_EmpName range
and return the corresponding value in the second column. For example, if A2
contains Coderre,Ron then the formula wil return MGR.

To make things more fool-proof....you might want to use Data Validation on
the name input cells to ensure that only correctly spelled, validly formatted
names are used.

Does that help?

***********
Regards,
Ron