Excel 2007 : return multiple values - using vlookup

I have a table of data where I want to summarize the data in a matrix type form where it looks up the name of the person and returns all associated IDs with that person, returning more than 1 if that condition exists...

Attached is a sample of the data and the summary that I was trying to accomplish where you will see my notes on the problem I am having. I am not sure if VLOOKUP is the best formula here and open to other suggestions.

Thanks!

Is there always going to be a maximum of 2 matches?

You are going to need VBA then.

Hit ALT+F11 and then go to Insert|Module and paste the below code in the Editor:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

close the VB editor

Then in E10 of the spreadsheet enter formula:

=SUBSTITUTE(TRIM(aconcat(IF($A$2:$A$7=$D10&";"&E$9,$B$2:$B$7,"")," "))," ",", ")

adjust the ranges to suit your data and then hold the CTRL+SHIFT keys down and hit ENTER, then copy across the columns.

You will need to widen the columns to see all matches.

Not really simpler... just not needing the VBA to consolidate all results...

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save