Lookup that return multiple values

[LostinVA]

Hi everyone! I'm trying to figure out how to look up a value in one column and return all unique values from a different column.

For example, in a workbook I have Sheet 1 with Column G listing multiple Vendor Names (starting on G7). On Sheet2, Column F also has Vendor Names and in Column J there are multiple Products. I'd like to create a formula on Sheet1, Column H7 where if there's a match to Sheet2, Column F, then it returns all values from Column J (Products).. If Sheet1, G7 is "Acme" for instance, then anywhere "Acme" is listed on Sheet2, Column F, it will return the values(s) from Column J (Products).. so the return could be something like "widgets, gizmos, trinkets"

Hope that makes sense. Appreciate everyone's time!

 

[SanjayGMusafir]

Update your current excel version in your Profile and here - This way it shall appear to everyone who visits this thread to help you.

Also upload some sample data

Your solution will depend on above two things

 

[shift-del]

Hi

Use a pivot table.