Excel formula to find and match the data

[SmallJack]

Hi Gurus,

Would need your kind expertise to help on this request.

Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas, please refer to below sample data.

I have some sample data in "Sheet1" and another sample data in "Sheet2".

The intended output is shown in "Sheet3".

For example: if "Student1" in Sheet1 match the data for "Student1" in Sheet2, then all the entries for "Student1" in Sheet2 will be copied to Sheet3 (ouput). The same thing goes to other students in the list (Sheet1).

Sheet1 (sample data):
Student1 | Student2 | Student3 | Student4 | Student5

Sheet2 (sample data):
Student2 | Student5 | Student3 | Student4 | Student1
Lesson1 | Lesson1 | Lesson11 | Lesson1 | Lesson1
Lesson8 | Lesson2 | Lesson2 | Lesson23 | Lesson10
Lesson3 | Lesson3 | Lesson31 | Lesson3 | Lesson3
Lesson10 | Lesson4 | Lesson4 | Lesson44 | Lesson4
Lesson5 | Lesson5 | Lesson52 | Lesson5 | Lesson15
Lesson6 | Lesson6 | Lesson6 | Lesson6 | Lesson6
...............Lesson7..................Lesson10 | Lesson7
...............Lesson20.................................Lesson11

Sheet3 (output sample):
Student1 | Student2 | Student3 | Student4 | Student5
Lesson1 | Lesson1 | Lesson11 | Lesson1 | Lesson1
Lesson10 | Lesson8 | Lesson2 | Lesson23 | Lesson2
Lesson3 | Lesson3 | Lesson31 | Lesson3 | Lesson3
Lesson4 | Lesson10 | Lesson4 | Lesson44 | Lesson4
Lesson15 | Lesson5 | Lesson52 | Lesson5 | Lesson5
Lesson6 | Lesson6 | Lesson6 | Lesson6 | Lesson6
Lesson7.................................Lesson10 | Lesson7
Lesson11................................................Lesson20

The sample data could be huge, so it's best if the formula can be made flexible.

Please refer to the attached file for some sample data.

Appeciate for any of your help and advice.

Thank you a lot in advance.


Regards,
Jack

[rwgrietveld]

You do not comply with the forum rules:
Please adjust your title to something more usefull.

... and please refer to your other thread. This might be helpfull.
http://www.excelforum.com/excel-prog...ientation.html

[teylyn]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.