Return all row information consist of the look up value

[reeNce]

I had created a table and example as shown below. The result output I require, if Sheet 1 name matches reference from Sheet 2 then it should display all value and rows from it else blank.

ps. Below is an example, in actual fact Sheet 1 & 2 is actually a different spreadsheet.

(Sheet 1)

Name	Color	Hobby	Pet
Peter
Lissy
John

(Sheet 2)

Name	Color	Hobby	Pet
Peter	Green		Dog
Peter	Blue	Badminton	Cat
Peter	Black	Soccer	Monkey
John	Blue	Swimming
Mary	Red		Cow

The output that I want from Sheet 1

Name	Color	Hobby	Pet
Peter	Green		Dog
Peter	Blue	Badminton	Cat
Peter	Black	Soccer	Monkey
Lissy
John	Blue		Swimming

[mcmahobt]

Based on you question, it is not clear to me what you want to see as a result. Do you want the result on Sheet 1? What results do you want? Currently, you have multiple entries for Peter on Sheet 2; how do you want those displayed?

I realize you attached a sample workbook, but again, it is not clear to me what you require.

[reeNce]

Yes, I would like the result to be display on Sheet 1 and according to the column. So if there are multiple entry, will like those to be display as shown on above output created.

[mcmahobt]

What I'm confused about is your results....they're the same as your Sheet 2 values, except with Lissy instead of Mary. What is the logic behind what you are trying to accomplish?

[reeNce]

I am trying to lookup a single value and return with multiple matching value, something like http://thinketg.com/how-to-return-mu...ch-or-vlookup/.
Basically, some of the value may not be appearing at Sheet2 in the actual file, this is just an example for illustration.

Sorry if I explain it not well.