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

 

[Monty85]

Also, on the above. I need this to work if the cell is blank as well, not just pick up the next populated cell.

HIghlighted cells is what my results should look like in this scenario.

TESTING - Export_Xml_Report.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	7/04/1915	Note Line 1		1234	Bruce Wayne	7/04/1915	Note Line 1		Note Line 3
3	1234	Bruce Wayne	7/04/1915
4	1234	Bruce Wayne	7/04/1915	Note Line 3
5	5678	Clark Kent	18/04/1977	Note Line 1
6	5678	Clark Kent	18/04/1977	Note Line 2
7	5678	Clark Kent	18/04/1977	Note Line 3
Sheet3

Cell Formulas
Range	Formula
G2	G2	=INDEX(B:B,MATCH(F2,A:A,0))
H2	H2	=INDEX(C:C,MATCH(F2,A:A,0))

 

[kevin9999]

Not sure if you can get away with not entering as array formulas (Ctrl+Shift+Enter) with 2016 - you might? With the formulas, don't use entire columns as references - just use enough to cover what you expect you'll need - I used 100 in the demo below, you can use 1000, 10K whatever. I also formatted the range I2:K2 to show zero values as blanks.

test.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	7/04/1915	Bruce Line 1		1234	Bruce Wayne	7/04/1915	Bruce Line 1		Bruce Line 3
3	1234	Bruce Wayne	7/04/1915			5678	Clark Kent	18/04/1977	Clark Line 1	Clark Line 2	Clark Line 3
4	1234	Bruce Wayne	7/04/1915	Bruce Line 3
5	5678	Clark Kent	18/04/1977	Clark Line 1
6	5678	Clark Kent	18/04/1977	Clark Line 2
7	5678	Clark Kent	18/04/1977	Clark Line 3
8
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=INDEX(B:B,MATCH(F2,A:A,0))
H2:H3	H2	=INDEX(C:C,MATCH(F2,A:A,0))
I2:I3	I2	=INDEX($D$2:$D$100,MATCH(TRUE,EXACT($F2,$A$2:$A$100),0))
J2:J3	J2	=INDEX($D$2:$D$100,SMALL(IF($F2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),2))
K2:K3	K2	=INDEX($D$2:$D$100,SMALL(IF($F2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),3))

 

[awoohaw]

@Monty85 , Is this strictly for a lookup issue, or are you trying to create a crosstab report with one record for each unique ID?

 

[Monty85]

@Monty85 , Is this strictly for a lookup issue, or are you trying to create a crosstab report with one record for each unique ID?

Just a look up issue I think.

The data is presented in multiple line but in order to complete the next step and load the data into the next system each ID needs to be just on the one line.

 

[Monty85]

Not sure if you can get away with not entering as array formulas (Ctrl+Shift+Enter) with 2016 - you might? With the formulas, don't use entire columns as references - just use enough to cover what you expect you'll need - I used 100 in the demo below, you can use 1000, 10K whatever. I also formatted the range I2:K2 to show zero values as blanks.

Thanks - yeah if it has to be an array formula I can live with it.

Is there a reason to not use entire columns in the formulas? Is this just an array thing or a good general rule?

 

[kevin9999]

Thanks - yeah if it has to be an array formula I can live with it.

Is there a reason to not use entire columns in the formulas? Is this just an array thing or a good general rule?

A good general rule in most cases (no point in getting Excel assess an entire column if no need to).

 

[awoohaw]

Just a look up issue I think.

The data is presented in multiple line but in order to complete the next step and load the data into the next system each ID needs to be just on the one line.

So, it is not a look up issue. What you want is to transform the data into a different shape.

 

[Monty85]

So, it is not a look up issue. What you want is to transform the data into a different shape.

Yeah, sorry just wasn't certain what the terms meant.

 

[awoohaw]

Okay, this converts your data into a lookup range with one record per line.
You MUST UPDATE the formulas as I have them in this worksheet to fit your starting cell.
In this the starting cells are F4, G4, H4, I4, you must change the subtraction the ROW() sections to match your starting cell...
THEN.. you must subtract the row number less one where that starting cell is, so, if you are starting in in Cell C2... the row formulas would be:
(ROW(C2)-1).

Similarly, the COLUMN function must also be adjusted. If, as above you start in cell C2, your column function components would be:
(COLUMN(C2)-3)

Book1
	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	2001-05-08	Note Line 1		1234	Bruce Wayne	37019	Note Line 1		Note Line 3
3	1234	Bruce Wayne	2001-05-08
4	1234	Bruce Wayne	2001-05-08	Note Line 3		1234	Bruce Wayne	2001-05-08	Note Line 1		Note Line 3
5	5678	Clark Kent	1990-07-08	Note Line 1		5678	Clark Kent	1990-07-08	Note Line 1	Note Line 2	Note Line 3
6	5678	Clark Kent	1990-07-08	Note Line 2		9876	George Jetson	1985-04-06			Note Line 3
7	5678	Clark Kent	1990-07-08	Note Line 3		8521	Mr. Spacely	1985-09-25	Note Line 1	Note Line 2
8	9876	George Jetson	1985-04-06			7419	Olive Oyl	1993-11-30		Note Line 2	Note Line 3
9	9876	George Jetson	1985-04-06
10	9876	George Jetson	1985-04-06	Note Line 3
11	8521	Mr. Spacely	1985-09-25	Note Line 1
12	8521	Mr. Spacely	1985-09-25	Note Line 2
13	8521	Mr. Spacely	1985-09-25
14	7419	Olive Oyl	1993-11-30
15	7419	Olive Oyl	1993-11-30	Note Line 2
16	7419	Olive Oyl	1993-11-30	Note Line 3
17
Monty85

Cell Formulas
Range	Formula
G2	G2	=INDEX(B:B,MATCH(F2,A:A,0))
H2	H2	=INDEX(C:C,MATCH(F2,A:A,0))
F4:F8	F4	=INDEX($A$2:$A$16,((ROW(F4)-3)*3)-2)
G4:G8	G4	=INDEX($B$2:$B$16,((ROW(G4)-3)*3)-2)
H4:H8	H4	=INDEX($C$2:$C$16,((ROW(H4)-3)*3)-2)
I4:K8	I4	=IF(INDEX($D$2:$D$16,((ROW(F4)-3)*3)-2+MOD((COLUMN(I4)-9),3))="","",INDEX($D$2:$D$16,((ROW(F4)-3)*3)-2+MOD((COLUMN(I4)-9),3)))