IF Statement or Lookup?

[CreeperKat]

I have a list of user ids in column A that have a department prefix. For example, HRSALLY (Human Resources Sally), SHJASON (Shipping Jason), ACGEORGE (Accounting George), etc. I want to be able to populate column B with the department managers name based on the department prefix.

The end result should be HRSALLY remains in Column A and column B has the HR Manager's name. Next row shows SHJASON in Column A and the Shipping Manager's name in column B, etc.



Suggestions on how to do?

[TJKarakowski]

Hey there-
I think you need to do two things:

-First you need to seperate the department from their name. I'm assuming each abbrevation is 2 letters. Highlight the list of names, go to Data>Text to columns> Set to "Fixed WIdth", and insert a line break between ac and alex. THis will give you columns with department and then employee name
example
A B
1 AC Alex
2 HR Jimmy
THen set up a table off to the side or on another sheet with department abbrevations and the boss's name next to them - make sure you sort your columns so that the abbrevations are in ascending alpabetical order(or else the VLOOKUP won't work).

D E
1 AC Tim Conway
2 HR Tina Collins
3 SH Mary Dess


Then go back to the original list and insert your vlookup

A B C
1 AC Alex =vlookup(A1,D1:E3,2)

Hope this helps!


etc.

Then insert a vl