lookup help - extracting values not from the first match in a dataset

[Monty85]

I'm having trouble trying to find a way to lookup values from the 2nd, 3rd, etc, matches in a dataset.

In the below example, i want to use a formula to extract the multiple note lines for each person.

The end result would have a column for each note but just the one row for each person;

This will need to work for Excel 2016.

I've found some suggested solutions which use INDEX/MATCH and i seem to be able to get it to work for one line but run into issues when copying the formula down. It was also a solution which required the use of CTRL-SHIFT-ENTER which wasn't ideal. If there's a way to avoid that, awesome!

Appreciate the help as always,
Monty

 

[AhoyNC]

The formulas below should work without CTRL-SHIFT-ENTER.
The formula in F2 will return the unique ID's from column A. However instead of using this long formula I'd probably just copy column A over to F and and use the REMOVE DUPLICATES from the ribbon under DATA.

You can just drag the formula in G2 down and over 1 column as needed.
Just drag the formula in I2 down and across 3 columns as needed.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Name	DOB	Notes		ID	Name	DOB	Note Line 1	Note Line 2	Note Line 3
2	1234	Bruce Wayne	4/17/1915	Note Line 1		1234	Bruce Wayne	4/17/1915	Note Line 1	0	Note Line 3
3	1234	Bruce Wayne	4/17/1915			5678	Clark Kent	4/18/1977	Note Line 1	Note Line 2	Note Line 3
4	1234	Bruce Wayne	4/17/1915	Note Line 3
5	5678	Clark Kent	4/18/1977	Note Line 1
6	5678	Clark Kent	4/18/1977	Note Line 2
7	5678	Clark Kent	4/18/1977	Note Line 3
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,IF(FREQUENCY(MATCH($A$2:$A$7,$A$2:$A$7,0),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($F$2:F2))),"")
G2:H4	G2	=IF($F2="","",INDEX(B$2:B$7,MATCH($F2,$A$2:$A$7,0),1))
I2:K3	I2	=INDEX($D$2:$D$7,AGGREGATE(15,6,(ROW($D$2:$D$7)-ROW($D$2)+1)/($A$2:$A$7=$F2),COLUMNS($I$2:I2)))

 

[bebo021999]

One way, for Ex2016 or older

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Name	DOB	Notes		ID	Name	DOB	Note Line 1	Note Line 2	Note Line 3
2	1234	Bruce Wayne	07 April 1915	Note Line 1		1234	Bruce Wayne	07 April 1915	Note Line 1		Note Line 3
3	1234	Bruce Wayne	07 April 1915			5678	Clark Kent	18 April 1977	Note Line 1	Note Line 2	Note Line 3
4	1234	Bruce Wayne	07 April 1915	Note Line 3
5	5678	Clark Kent	18 April 1977	Note Line 1
6	5678	Clark Kent	18 April 1977	Note Line 2
7	5678	Clark Kent	18 April 1977	Note Line 3
8
9
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$7),),0)),"")
G2:G13	G2	=IF(F2="","",VLOOKUP($F2,$A$2:$D$7,2,0))
H2:H13	H2	=IF(G2="","",VLOOKUP($F2,$A$2:$D$7,3,0))
I2:K13	I2	=IF($F2="","",IF(INDEX($D:$D,AGGREGATE(15,6,ROW($A$2:$A$7)/($A$2:$A$7=$F2),COLUMNS($A:A)))=0,"",INDEX($D:$D,AGGREGATE(15,6,ROW($A$2:$A$7)/($A$2:$A$7=$F2),COLUMNS($A:A)))))