Extract Multiple Leadership positions from a list for the same person

[bearcub]

I have a list of members where some hold more than one position. If I use a regular Vlookup or Index Match formula I will only get the first one and will be repeated.

For Example, Jane Doe is a president , secretary and bargaining chair.

I have 3 records in my list where I would expect to populate record 1 with president, record 2 with secretary and record three with bargaining chair. I know that the small or aggregate function is used for this formula but I can't remember how to set it up specifically.

Thank you for your help,

Michael

 

[AhoyNC]

See if this helps:

Formula in B8 needs to be entered with CTRL-SHIFT-ENTER.
Formula in B10 using aggregate just ENTER.

Drag formulas across columns as needed.

Excel Workbook
	A	B	C	D
1	Name	Post.
2	Jane Doe	President
3	Sam Smith	VP
4	Jane Doe	Sec.
5	Jane Doe	Bar. Chair
6
7	Name	Record1	Record2	Record3
8	Jane Doe	President	Sec.	Bar. Chair
9
10	Jane Doe	President	Sec.	Bar. Chair
Sheet

 

[bearcub]

I was able to get the first position to pull properly but I'm getting a NUM error for the rest of the members.

This is the formula that I'm using:

=+INDEX('LocalStateSCC Falcon Positions'!$C$3:$C$553,AGGREGATE(15,6,(ROW($A$2:$CA$553)-ROW($A$2)+1)/('LocalStateSCC Falcon Positions'!$A$3:$A$553=[@[ind_cst_recno]]),ROWS(F$5:F5)))

The LocalStateSCC is the source table and I need to populate members on another sheet that starts at row 2 - instead of row 3 as on the source sheet. Is there something wrong with the way i set up my formulas

Thank you

Michael

 

[AhoyNC]

See if this helps.
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B
1	Name	Jane Doe
2	President
3	Sec.
4	Bar. Chair
Sheet2

Excel Workbook
	A	B	C
1
2	Name		Post.
3	Jane Doe		President
4	Sam Smith		VP
5	Jane Doe		Sec.
6	Jane Doe		Bar. Chair
LocalStateSCC Falcon Positions