Return Multiple Values For a Single search

[Darkonius]

Ok here is my problem in general. I have an excel Sheet with a List of sales people (12) and a list of around 1500 potential clients, we have assigned clients to salespeople, and I would now like to create a separate table for each salesperson with a list of each client pulled from the master list. I have tried using the =vlookup ut it will only return the first occurrence of the salespersons name and ignore everything thereafter.

I found what I thought to be a solution with this:
=INDEX('Sales'!$G$6:$G$13, SMALL(IF(A1='Sales'!$D$6:$D$13, ROW('Sales'!$D$6:$D$13)-MIN(ROW('Sales'!$D$6:$D$13))+1, ""), ROW(A1)))

This returns the value from the second column from the master file each time it finds "Tom" (Cell A1=Tom) however the limitation of this is that I can not get it to return more than 7 occurrences, it seems to blow the list up when I increase the cell range beyond that.

I finally got frusterated trying to make that work , so I developed a sheet where all of the salespeople are listed in the A column, Clients in the B column all starting at Row 2, and in C1 the salespersons name and in C2 writing this statement =If(A2=C1,B2)
I have followed this format across (more salespeople) and down using $ to lock where needed and that part works fine, but now I want to return a list without all of the "#N/A", "False" and blanks left behind in the column.
In other words I want to create a complete list from a column (about 30 names) where the column has numerous blanks, "#N/A", and "False" statements in it.

I hope that all makes sense and someone can help. If it comes to it I can work up a "dummy" sheet of what I am trying to do and link it. But I am hoping this is a simpler problem than I am making it and someone understands it better than I.

Thanks in advance for the help!

[FDibbins]

Hi and welcome to the forum

Short answer...=iferror(If(A2=C1,B2),"")

Long answer, upload a sample workbook for us (no sensitive info) to take a look at and see if we can offer any more suggestions?

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Darkonius]

Sorry I should have just done this to begin with. Attached is a "Dummy" file representing what I want to accomplish in a nutshell. On page one 'MasterList' is the data pool I want to pull from. Column C "Sales Person" is manually configured depending on who is assigned, all other data is linked forward from a database.

Page 2 outlines how I would like the information returned. The highlighted yellow cells represent cells that I would like to fill based on criteria. The salesperson assigned and the company the Client works for.

Hope this clears things up.

[FDibbins]

While I work on this, take a look at using a Pivot Table, and see if that will get you closer to what you want.

sales person for column labels
company for row labels
client for values

[FDibbins]

OK try this...

in sheet1 F5, copied down...
=C5&" "&COUNTIFS($C$5:C5,C5,$E$5:E5,E5)&" "&E5

Then use a helper column in A, D, G etc for this...(you can format the text to white so it looks like its invisible)...
=IF(B5="",A4,B5)

And finally in C5, copied down and across to F, I L etc...
=IFERROR(INDEX(MasterList!$D:$D,MATCH(B$3&" "&COUNTIF(A$5:A5,A5)&" "&A5,MasterList!$F:$F,0),1),"")

[Darkonius]

OK try this...

in sheet1 F5, copied down...
=C5&" "&COUNTIFS($C$5:C5,C5,$E$5:E5,E5)&" "&E5

Then use a helper column in A, D, G etc for this...(you can format the text to white so it looks like its invisible)...
=IF(B5="",A4,B5)

And finally in C5, copied down and across to F, I L etc...
=IFERROR(INDEX(MasterList!$D:$D,MATCH(B$3&" "&COUNTIF(A$5:A5,A5)&" "&A5,MasterList!$F:$F,0),1),"")

Thank you!! This is exactly what I was trying to accomplish!!