Please help with trying to take data from Sheet2, based on data from Sheet1, then pasting it into Sheet1

[BrisAdrian]

I am not Excel Savvy at all and have spent the better part of the weekend trying to learn/figure this out but have hit a wall.

Work has tasked me with creating a spreadsheet that holds the records of 1000 or so employees and their regular tests.
To achieve this I have created a manual entry database, however when showing and explaining to my colleagues how to update this, they all struggled. To the point where I was asked to create a tutorial on how to find an employee, scroll across to his test date and then how to copy and paste that.

I think it would be easier if I create a "cover sheet" where we can only enter in an employees pay number and have the sheet retrieve data. To prevent potentially devastating human error.

Here is an example of the cover page:

Employee Number	Number is entered here (12345)
Employee Name	Name is auto populated from another sheet
Employee Role	Role is auto populated from another sheet
Test Date	Populated from another sheet (latest)
Test Result	Populated from another sheet (latest)

<tbody>
</tbody>

The only cell I want to leave unlocked is the one where we can put in someones pay number (this I can do)

On a second sheet is where the data is kept:

Employee Number	Name	Role	Test 1	Test 1 Result	Test 2	Test 2 Result
12345	Smith	IT	1/1/2015	55	1/3/2015	50
45678	Doe	HR	4/4/15	5	5/5/15	6

<tbody>
</tbody>

So what I want to achieve is to retrieve the most resent (right most) test AND result per person when entering a Pay number in Sheet 1.

If this is possible I would be so incredibly grateful for any help!

So far I have been trying VLOOKUP with no luck, I have been reading about MATCH and INDEX but I haven't quite got my head around it to make it happen.

Thanks again for any help!

-Adrian

 

[niges]

For VLOOKUP to work you will need the employee numbers to be sorted in order. If they are try putting TRUE at the end of the formula - i.e. =VLOOKUP(D14,D8:E11,2,TRUE)

 

[MARZIOTULLIO]

HI Format Test Date :Date

Employee Number	12345
Employee Name	=VLOOKUP($B$1,Sheet2!$A$2:$G$3,row(),FALSE) down
Employee Role
Test Date
Test Result

<tbody>
</tbody>

 

[BrisAdrian]

Thanks so much guys. Got it going.

... now for the rest of this monster!