using vlookup to display multiple results

[chapperzz]

Hi,

I have set up a vlookup, which pulls in a row of data from a unique user from a table of results. At the moment my user chooses from a list of exams to display the certain data related to a unique user: =VLOOKUP($B9,results!$A$3:$K$70,3,FALSE)


[TABLE="width: 974"]
<tbody>[TR]
[TD]Choose Exam from List to Display Indv Student Results
[/TD]
[TD]
[/TD]
[TD]Surname
[/TD]
[TD]First Name
[/TD]
[TD]Student ID
[/TD]
[TD]%Score
[/TD]
[TD]Result
[/TD]
[TD]Grade
[/TD]
[/TR]
[TR]
[TD]Evans L2 Spreadsheet Software MS Excel 2010 Cert UK Eng BCS112893
[/TD]
[TD]
[/TD]
[TD]Evans
[/TD]
[TD]Natasha
[/TD]
[TD]BCS112893
[/TD]
[TD]66%
[/TD]
[TD]FAIL
[/TD]
[TD]U
[/TD]
[/TR]
[TR]
[TD]Davies L2 Presentation Software MS PowerPoint 2010 Cert UK Eng BCS112894
[/TD]
[TD]
[/TD]
[TD]Davies
[/TD]
[TD]Mollie
[/TD]
[TD]BCS112894
[/TD]
[TD]91%
[/TD]
[TD]PASS
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]Davies L2 Presentation Software MS PowerPoint 2010 Cert UK Eng BCS112894
[/TD]
[TD]
[/TD]
[TD]Davies
[/TD]
[TD]Mollie
[/TD]
[TD]BCS112894
[/TD]
[TD]91%
[/TD]
[TD]PASS
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]Davies L2 Schools Improving Productivity MS Office 2010 Cert UK Eng BCS112894
[/TD]
[TD]
[/TD]
[TD]Davies
[/TD]
[TD]Mollie
[/TD]
[TD]BCS112894
[/TD]
[TD]82%
[/TD]
[TD]PASS
[/TD]
[TD]B
[/TD]
[/TR]
</tbody>[/TABLE]


What I would like to happen is when the user chooses a particular exam the lookup returns ALL the data relating to a unique user.

[TABLE="width: 976"]
<tbody>[TR]
[TD]Click 1st Column to Display Indv Student Results
[/TD]
[TD]
[/TD]
[TD]Surname
[/TD]
[TD]First Name
[/TD]
[TD]Student ID
[/TD]
[TD]%Score
[/TD]
[TD]Result
[/TD]
[TD]Grade
[/TD]
[/TR]
[TR]
[TD]Batty L2 Presentation Software MS PowerPoint 2010 Cert UK Eng BCS112896
[/TD]
[TD]
[/TD]
[TD]Batty
[/TD]
[TD]Matthew
[/TD]
[TD]BCS112896
[/TD]
[TD]45
[/TD]
[TD]FAIL
[/TD]
[TD]U
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

The problem I am getting is that other than the first line the results return N/a

The lookup formula for the top line is =VLOOKUP($B$17,results!$A$3:$K$3,3,FALSE), this pulls data from a single row in the table.

The lookup is then changed to select and display the next row of data =VLOOKUP($B$17,results!$A$4:$K$4,3,FALSE)

Is this possible or do I need a different solution?

Thanks

 

[Special-K99]

Why dont you just use Autofilter on the Surname?