Vlookup/index/match + concatenation

Hello all,

I'm having trouble figuring out a specific formula. I want to do a VLOOKUP (or something similar) that matches a value in a table in a separate sheet, and returns a specific value, but the kicker is that it will concatenate the values if there are two or more different values matching the initial lookup value.

Please see the attached spreadsheet example. I would run the formula in B2 of the first tab, and it would reference the table in the second tab. This is much smaller scale example than what I intend on applying to. There could potentially be more than 2 matches for a specific family number.

Thanks in advance.

When I ran that, it gave me the Product #'s from the second tab. What I have in Column B in the first tab is an example of how I want it to look after the formula is ran. As you can see, it concatenated the instances where two different contacts matched the same family. That's what I'm looking for.

Okay. Basically, I want a function that isn't confined to the limitations of a simple VLOOKUP. If I were to VLOOKUP on just the family column, it will return only the value of the first occurrence of a match. So if I ran it in B2 of the first tab and dragged it down, for Family P555, it would list "Smith" as the match and display that in the cell. I want something that will account for all of the matches for a specific family number, so for P555, it will list "Smith, Johnson" when the formula runs in B11, since in the reference table in the second tab, both Smith and Johnson contain the Family value of P555. Does that make more sense? I want it to list all unique matches in a concatenation type form, without generating duplicates.

Is there anyway to modify that so that it won't list the same name twice? In rows 4 and 11, the same name is duplicated. For the purposes of this, we can assume that there is only one person named Black and one person named Smith.