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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Number[/TD]
[TD]Number is entered here (12345)[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]Name is auto populated from another sheet[/TD]
[/TR]
[TR]
[TD]Employee Role[/TD]
[TD]Role is auto populated from another sheet[/TD]
[/TR]
[TR]
[TD]Test Date[/TD]
[TD]Populated from another sheet (latest)[/TD]
[/TR]
[TR]
[TD]Test Result[/TD]
[TD]Populated from another sheet (latest)[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Number[/TD]
[TD]Name[/TD]
[TD]Role[/TD]
[TD]Test 1[/TD]
[TD]Test 1 Result[/TD]
[TD]Test 2[/TD]
[TD]Test 2 Result[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Smith[/TD]
[TD]IT[/TD]
[TD]1/1/2015[/TD]
[TD]55[/TD]
[TD]1/3/2015[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]Doe[/TD]
[TD]HR[/TD]
[TD]4/4/15[/TD]
[TD]5[/TD]
[TD]5/5/15[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 657"]
<tbody>[TR]
[TD]Employee Number[/TD]
[TD]12345[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]=VLOOKUP($B$1,Sheet2!$A$2:$G$3,row(),FALSE) down[/TD]
[/TR]
[TR]
[TD]Employee Role[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test Result[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[BrisAdrian]

Thanks so much guys. Got it going.

... now for the rest of this monster!