Need Help- Dupe records Index/ Match function

[rrenzi]

What I need to do. I download a list of 'special' subscribers. I need to write a formula that will transfer these subscribers to a sheet for each group. A subscriber can have one product or multiple products. A group may have zero, one or multiple 'special' subs. I have to write a formula and I can not copy and paste even though that might be easier... lol. There is a one to one association of subno, groupno and name. A sub can not be in multiple groups. Each group has only one name. I am using the Index Match function, but it is not working right when I have multiple members with more than one product. Hopefully the link to my spreadsheet works

demo4.xlsx

Shared with Dropbox

www.dropbox.com

 

[Toadstool]

Hi RRenzi,

I'm not sure if I'm following correctly but I believe your use of the SMALL formula in "AB CO" A33 is working correctly but you're having problems retrieving the other columns using just INDEX & MATCH?

I'd suggest adding a column for "Info Row" (let's say column Q) populated with your
{=SMALL(IF(($B$1=Info!$X$5:$X$1000), MATCH(ROW(Info!$X$5:$X$1000), ROW(Info!$X$5:$X$1000)), ""),ROWS($BA$1:$BA1))}
then all the other columns become just INDEX & MATCH using that row number, e.g.
A33 would become =INDEX(Info!J$5:J$1000,$Q33)
B33 would become =INDEX(Info!K$5:K$1000,$Q33)
etc.

 

[AhoyNC]

Since you have Excel 365 maybe try the FILTER function.
So on your ABC sheet:

=FILTER(Info!$J$5:$X$1000,Info!$X$5:$X$1000=$B$1)