Lookup for values and return all matching rows

Question

Is there a way to look up for values(sheet2) in sheet1 and copy all the matching rows to a new sheet (sheet3)?

Sheet1
Name    Hours   Group
Joy     12  AA
Allen   23  AA
Tom 34  AA
Amy 45  AX
Chris   6   AD
Allen   7   FG
Tom 8   GH
Joy     8   JK
Amy 23  UY
Jack    11  AA


Sheet 2 (col A):
Joy
Allen
Amy
Chris

Sheet 3 (expected o/p)
Joy     12  AA
Joy     8   JK
Allen   23  AA
Allen   7   FG
Amy 45  AX
Amy 23  UY
Chris   6   AD

Answer 1

What you try is actually filter sheet1 by the names of sheet2

Make use of Advanced Filters to achieve the result of sheet3.
But the restrictions are …

• it has to be done in the same sheet (Excel restricts it)
  so you need to copy the result to sheet3 after filtering it.
• you need to sort it by name before or after

The following image describes what parameters I used for advanced filter.
(Sorry for the german screenshot, I think you will figure it out.)

Note that you don't necessarily copy the result to another location, but can do a reversible filtering just in place of the original data. Which one to use depends on what you are going to do with that data later.

Also note, that the headlines (like 'Name') of the criteria range have to match the list range. The advanced filter uses them to find the column to filter on.