Entering a staff number to get their name, job role, shift

[Broady]

Hi,


I would like to know how to create a 2 page workbook that will consist of sheet 1 'Data Sheet'. This will contain the following information:


Employee No | First Name | Last Name | Job Role | Shift

111 | Fred | Blogs | Cleaner | Days


Sheet 2 'Input Sheet' I would like to be enter their employee number in the 'A' cells, B, C, D & E will self populate their details. Meaning I only have input their number for their full details to show.


Can someone help me with this one?


Thank you

 

[Montrey]

In the first name column..

=index(firstnamerange,match(employeenumber,employeenumberrange,0))

Do this for each section and all done.

Just change the firstnamerange do the next column range.

 

[shrivallabha]

You can also implement VLOOKUP like:

=VLOOKUP(A2,'Data Sheet'!$A$1:$E$200,2,0

where you will have to adjust the number after 2nd comma like:

3 for Last Name

4 for Job Role

5 for Shift

 

[bobhc]

=VLOOKUP(A2,'Data Sheet'!$A$1:$E$200,2,0...........but you may wish to put a closing parenthesis at the end of the vlookup

 

[bobhc]

You may find this very useful


http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

 

[shrivallabha]

Just to emphasize Closing is as important as Opening. Thanks Bob.

 

[Broady]

Thank you folks.