Excel 2007 : return multiple values - using vlookup

**heatherromo** · 03-01-2012, 01:33 PM

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!

**NBVC** · 03-01-2012, 01:43 PM

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

**heatherromo** · 03-01-2012, 01:45 PM

It is possible that there may be more than 2.

**NBVC** · 03-01-2012, 01:51 PM

You are going to need VBA then.

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

Please Login or Register  to view this content.

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.

**heatherromo** · 03-01-2012, 02:23 PM

Is there a simpler solution if there were a max of 2? I am curious

**NBVC** · 03-01-2012, 02:29 PM

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

**heatherromo** · 03-01-2012, 02:38 PM

that works...even though I have never used VBA before, I think I have to start learning....Thank you very much

**NBVC** · 03-01-2012, 02:46 PM

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