Transposing Correlating Data from Adjacent Cells via Formula

Blank Schedule.xlsm

I am redoing our company's schedule spread sheet and using drop-down menus to simplify the process of making the schedule each day. I have a master sheet that contains the names and employee numbers of all our employees in two separate columns, and am using those lists to create the drop-down menus for the actual schedule sheet. I have made it so you can select any employee from the drop-down, but I cannot figure out how to get the correct employee number to appear in the adjacent cell whenever you select an employee from the list. How can I do this?

I cannot simply merge the cells since the employees name must be color coded for organizational purposes and the cell that contains their employee number cannot be color-coded.

On the personnel roster, switch the columns so the employee name is in Column A, and the Number is in Column B.

Then on sheet "1", use a vlookup function.

For example, in cell H5, I put

Please Login or Register  to view this content.

Then, whenever you select an employee in I5, it will auto-populate his employee number in H5. Copy the formula down (and copy-paste it into other columns too).