VLOOKUP: Return multiple values?

[Flosion]

Hello,

I'm having trouble trying to work this out. Here is a sample of what I'm working with:

Name	Product
Jacob	Egg
Mervin	Carrot
Kirby	Broccoli
Jacob	Coffee
Mervin	Juice
Mervin	Bread
John	Lettuce
John	Jam
Raphael	Egg

I would like to have another column showing 'Name' but with the duplicates removed. (I've been doing this manually, is there a way to automate this?)

I would then like to return all Products that correspond to the name, of which there may be multiple, and present them horizontally in the next few columns next to the name. (I've attached the spreadsheet which shows my expected result if this is unclear)

Hopefully it will then show something like this:

Name	Product 1	Product 2	Product 3
Jacob	Egg	Coffee
Mervin	Carrot	Juice	Bread
Kirby	Broccoli
John	Lettuce	Jam
Rapha	Egg

VLOOKUP seemed to fit for this task, but I can't figure out a way to have it work with multiple values being returned. Please help!

Thanks.

[AlKey]

Enter array formula in F2 and copy across and down

Please Login or Register  to view this content.

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Please see attached file

To get list of unique names
Enter in E2 and copy down

Please Login or Register  to view this content.

[dflak]

You can also press a pivot table into service for this requirement. It is not as compact, but it is open ended meaning a person can have as many products as you have columns on the spreadsheet. Also it gives you summary information. Only one person likes bread while 4 like juice and Arlene only likes one thing while Deana likes three.

[Flosion]

Thanks for your help, that formula works perfectly! Not that I understand how, haha

If you care to explain the process that the formula goes through, I would be very grateful. I would love to apply these functions like this in the future.

Using a pivot table is also something I had not thought of, as I am unfamiliar with them too!

Thanks again all.