Index Match data to provide multiple results in columns

[lomond44]

I need some help with a spreadsheet that contains 2 worksheets.

The first sheet contains part numbers and the assemblies that they are used in. Each part number can be used in many assemblies.

On the 2nd sheet is a list of just the part numbers without duplicates. The aim is to create columns to the right of each part number that identifies all the assemblies that the part is used in.

I have looked at this link How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group but I do not want the results to be down a column but to go across the row.

Any help appreciated

 

[Aladin Akyurek]

Thanks this has been a great help.

You are welcome.

 

[awsmitty]

Perfect, I love this stuff, Aladin, many thanks

 

[Aladin Akyurek]

Perfect, I love this stuff, Aladin, many thanks

Glad to hear that. Thanks.

 

[bobby35k]

Aladin I think you might be the answer to my prayers.

I have a similar issue.

I have 2 sheets, Master & Participants.

Master has all the information pertaining to the account spread across columns A-AT. With column A being the Acct#.

Participants has all the information pertaining to each participant for each account, but each account can have multiple participants (Usually about 3 per account and a max of 10 per my countif test. Spread across columns A-AA (27 columns). Again with column A being Acct#

I need to append each participants data to the end of the Master sheet starting in column AU, then if there is more than one participant the next participant would start in column BU, then CU, etc.


Here are some screen shots if that helps but it is only a snippet.

Excel 2010
	A	B	C	D
1	Account Number	Account Status	IP	Registration Type
2	ABC001004	Open	905	TRANSFER ON DEATH ( INDIVIDUAL )
3	ABC001012	Open	905	JOINT
4	ABC001053	Open	726	PARTNERSHIP
5	ABC001061	Open	905	CORPORATION
6	ABC001079	Open	905	TRANSFER ON DEATH ( INDIVIDUAL )
7	ABC001103	Open	905	TRANSFER ON DEATH ( INDIVIDUAL )
8	ABC001111	Open	905	INDIVIDUAL
9	ABC001129	Open	305	INDIVIDUAL
10	ABC001137	Open	726	DLJ RETIREMENT ACCOUNT
11	ABC001145	Open	726	INDIVIDUAL
12	ABC001152	Open	726	INDIVIDUAL
13	ABC001186	Open	307	INDIVIDUAL
14	ABC001202	Open	306	DLJ RETIREMENT ACCOUNT
15	ABC001210	Open	307	DLJ RETIREMENT ACCOUNT
16	ABC001236	Open	905	DLJ RETIREMENT ACCOUNT
17	ABC001244	Open	905	DLJ RETIREMENT ACCOUNT
Master

Excel 2010
	A	B	C	D	E	F
1	Acct #	Customer Role	Participant	Participant Role Name	Beneficiary %	DOB
2	ABC001004	PRIMARY ACCOUNT HOLDER	Edite Smith	CUSTOMER	0%	2/26/1975
3	ABC001004	INTERESTED PARTY	Timmy Smith	INTERESTED PARTY	0%	2/27/1975
4	ABC001012	PRIMARY ACCOUNT HOLDER	Robert Riley	CUSTOMER	0%	2/28/1975
5	ABC001012	SECONDARY ACCOUNT HOLDER	Nancy Riley	CUSTOMER	20%	1/2/2016
6	ABC001079	BENEFICIARY	Kimberly Doe	CUSTOMER	20%	1/3/2016
7	ABC001079	BENEFICIARY	Scott Doe	CUSTOMER	15%	1/4/2016
8	ABC001079	BENEFICIARY	Suzanne Doe	CUSTOMER	15%	1/5/2016
9	ABC001079	BENEFICIARY	Erik Doe	CUSTOMER	15%	1/6/2016
10	ABC001079	PRIMARY ACCOUNT HOLDER	John Doe	CUSTOMER	0%	1/7/2016
11	ABC001079	BENEFICIARY	Randy Doe	CUSTOMER	15%	1/8/2016
Participants

Thank you in advance (If this is even possible that is)

 

[Aladin Akyurek]

@bobby35k

It's not clear how these two exhibits relate and how the output should look like. Care to elaborate?

 

[bobby35k]

Account Number

Account Status

IP

Registration Type

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

ABC001004

Open

905

TRANSFER ON DEATH ( INDIVIDUAL )

PRIMARY ACCOUNT HOLDER

Edite Smith

CUSTOMER

0%

2/26/1975

INTERESTED PARTY

Timmy Smith

INTERESTED PARTY

0%

2/27/1975

ABC001012

Open

905

JOINT

PRIMARY ACCOUNT HOLDER

Robert Riley

CUSTOMER

0%

2/28/1975

SECONDARY ACCOUNT HOLDER

Nancy Riley

CUSTOMER

20%

1/2/2016

ABC001079

Open

905

TRANSFER ON DEATH ( INDIVIDUAL )

PRIMARY ACCOUNT HOLDER

John Doe

CUSTOMER

0%

1/7/2016

BENEFICIARY

Scott Doe

CUSTOMER

15%

1/4/2016

BENEFICIARY

Kimberly Doe

CUSTOMER

20%

1/3/2016

BENEFICIARY

Suzanne Doe

CUSTOMER

15%

1/5/2016

BENEFICIARY

Erik Doe

CUSTOMER

15%

1/6/2016

BENEFICIARY

Randy Doe

CUSTOMER

15%

1/8/2016

<tbody>
</tbody>

So for example if you look at account number ABC001012 it is a joint account with 2 account holders. So on the Master sheet I want each account holder in the same row as that account number one after another. Some rows "accounts" may only have 1 participant while others will have many. The participants sheet is a fixed number of 27 columns.

I pasted above what the result should appear like. Except my sheets have a lot more columns.

Thank you for replying to my post. (Also I hope my data above comes out right, I am not currently on my work computer like I was earlier)

 

[Aladin Akyurek]

Account Number

Account Status

IP

Registration Type

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

Customer Role

Participant

Participant Role Name

Beneficiary %

DOB

ABC001004

Open

905

TRANSFER ON DEATH ( INDIVIDUAL )

PRIMARY ACCOUNT HOLDER

Edite Smith

CUSTOMER

0%

2/26/1975

INTERESTED PARTY

Timmy Smith

INTERESTED PARTY

0%

2/27/1975

ABC001012

Open

905

JOINT

PRIMARY ACCOUNT HOLDER

Robert Riley

CUSTOMER

0%

2/28/1975

SECONDARY ACCOUNT HOLDER

Nancy Riley

CUSTOMER

20%

1/2/2016

ABC001079

Open

905

TRANSFER ON DEATH ( INDIVIDUAL )

PRIMARY ACCOUNT HOLDER

John Doe

CUSTOMER

0%

1/7/2016

BENEFICIARY

Scott Doe

CUSTOMER

15%

1/4/2016

BENEFICIARY

Kimberly Doe

CUSTOMER

20%

1/3/2016

BENEFICIARY

Suzanne Doe

CUSTOMER

15%

1/5/2016

BENEFICIARY

Erik Doe

CUSTOMER

15%

1/6/2016

BENEFICIARY

Randy Doe

CUSTOMER

15%

1/8/2016

<tbody>
</tbody>

So for example if you look at account number ABC001012 it is a joint account with 2 account holders. So on the Master sheet I want each account holder in the same row as that account number one after another. Some rows "accounts" may only have 1 participant while others will have many. The participants sheet is a fixed number of 27 columns.

I pasted above what the result should appear like. Except my sheets have a lot more columns.

Thank you for replying to my post. (Also I hope my data above comes out right, I am not currently on my work computer like I was earlier)

I think you should sollicite VBA for the arrangement of records you want (You might even want to start a new thread on this problem.).

 

[CORDELL_III]

I have read through this and several other sites to find an answer to my problem that seems like it should be solved with your answers here, but after 2 days of searching I can't get it to work. PLEASE HELP!

I have a spreadsheet of records with a Salesman assigned to each; this information is formatted into a table using Excel for MAC 2016. On the next sheet I want to make several lists of the customer names on the records that correspond to the Salesman and their respective statuses. I have tried to use the formulas you've noted in this thread, but the data list that is returned is erratic at best.


SHEET1

Date	Last Name	CS	Status
3/21	Harris	SALESMAN1	APPROVED
3/23	Calkins	SALESMAN2	APPROVED
3/23	Weaver	SALESMAN1	APPROVED

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

SHEET2

SALESMAN APPROVED

SALESMAN1 HARRIS
WEAVER
... ETC ....

This is my most recent attempt:

=IFERROR(INDEX('SHEET!'!$B$2:$B$600,SMALL(IF('SHEET!'!$C$2:$C$600=$B$1, IF('SHEET1'!$D$2:$D$600="APPROVED",ROW('SHEET1'!$B$2:$B$600)-ROW('SHEET1'!$B$2)+1)), ROWS($B$3:B10))),"")

but it is not even close; it returns a last name, but it is not the first in the list, and repeats the same result down the entire results column ... if at all possible I'd also rather use Table references rather than the fixed cell references and ranges; something like this.

=IFERROR(INDEX(Table3[Last Name],SMALL(IF(Table3[CS]=$B1,ROW(Table3[Last Name])-ROW(Table3[@CS])+1),
ROW(Table3[[CS]:[CS]]))),"")

Thank you!

 

[CORDELL_III]

SHEET2

SALESMAN	SALESMAN1
APPROVED	HARRIS
	WEAVER
	ETC …

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

SHEET2

SALESMAN	SALESMAN1
APPROVED	HARRIS
	WEAVER
	ETC …

<tbody>
</tbody>

Let A:D of SHEET1 house the input data.

SHEET2

A1: SALESMAN
B1: SALESMAN1

A2: STATUS
B2: APPROVED

In A3 enter:

=COUNTIFS(SHEET1!C2:C400,B1,SHEET1!D2:D400,B2)

In A5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$5:A5)<=$A$3,INDEX(SHEET1!$B$2:$B$400,SMALL(IF(SHEET1!$C$2:$C$400=$B$1,IF(SHEET1!$D$2:$D$400=$B$2,ROW(SHEET1!$B$2:$B$400)-ROW(SHEET1!$B$2)+1)),ROWS($A$5:A5))),"")