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

Monty85

New Member
Joined
May 6, 2019
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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.

1680232604881.png


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

1680232697953.png


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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
ABCDEFGHIJK
1IDNameDOBNotesIDNameDOBNote Line 1Note Line 2Note Line 3
21234Bruce Wayne7/04/1915Note Line 11234Bruce Wayne7/04/1915Note Line 1Note Line 3
31234Bruce Wayne7/04/1915
41234Bruce Wayne7/04/1915Note Line 3
55678Clark Kent18/04/1977Note Line 1
65678Clark Kent18/04/1977Note Line 2
75678Clark Kent18/04/1977Note Line 3
Sheet3
Cell Formulas
RangeFormula
G2G2=INDEX(B:B,MATCH(F2,A:A,0))
H2H2=INDEX(C:C,MATCH(F2,A:A,0))
 
Upvote 0
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
ABCDEFGHIJK
1IDNameDOBNotesIDNameDOBNote Line 1Note Line 2Note Line 3
21234Bruce Wayne7/04/1915Bruce Line 11234Bruce Wayne7/04/1915Bruce Line 1 Bruce Line 3
31234Bruce Wayne7/04/19155678Clark Kent18/04/1977Clark Line 1Clark Line 2Clark Line 3
41234Bruce Wayne7/04/1915Bruce Line 3
55678Clark Kent18/04/1977Clark Line 1
65678Clark Kent18/04/1977Clark Line 2
75678Clark Kent18/04/1977Clark Line 3
8
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=INDEX(B:B,MATCH(F2,A:A,0))
H2:H3H2=INDEX(C:C,MATCH(F2,A:A,0))
I2:I3I2=INDEX($D$2:$D$100,MATCH(TRUE,EXACT($F2,$A$2:$A$100),0))
J2:J3J2=INDEX($D$2:$D$100,SMALL(IF($F2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),2))
K2:K3K2=INDEX($D$2:$D$100,SMALL(IF($F2=$A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2)+1),3))
 
Upvote 0
@Monty85 , Is this strictly for a lookup issue, or are you trying to create a crosstab report with one record for each unique ID?
 
Upvote 0
@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.
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJK
1IDNameDOBNotesIDNameDOBNote Line 1Note Line 2Note Line 3
21234Bruce Wayne2001-05-08Note Line 11234Bruce Wayne37019Note Line 1Note Line 3
31234Bruce Wayne2001-05-08
41234Bruce Wayne2001-05-08Note Line 31234Bruce Wayne2001-05-08Note Line 1 Note Line 3
55678Clark Kent1990-07-08Note Line 15678Clark Kent1990-07-08Note Line 1Note Line 2Note Line 3
65678Clark Kent1990-07-08Note Line 29876George Jetson1985-04-06  Note Line 3
75678Clark Kent1990-07-08Note Line 38521Mr. Spacely1985-09-25Note Line 1Note Line 2 
89876George Jetson1985-04-067419Olive Oyl1993-11-30 Note Line 2Note Line 3
99876George Jetson1985-04-06
109876George Jetson1985-04-06Note Line 3
118521Mr. Spacely1985-09-25Note Line 1
128521Mr. Spacely1985-09-25Note Line 2
138521Mr. Spacely1985-09-25
147419Olive Oyl1993-11-30
157419Olive Oyl1993-11-30Note Line 2
167419Olive Oyl1993-11-30Note Line 3
17
Monty85
Cell Formulas
RangeFormula
G2G2=INDEX(B:B,MATCH(F2,A:A,0))
H2H2=INDEX(C:C,MATCH(F2,A:A,0))
F4:F8F4=INDEX($A$2:$A$16,((ROW(F4)-3)*3)-2)
G4:G8G4=INDEX($B$2:$B$16,((ROW(G4)-3)*3)-2)
H4:H8H4=INDEX($C$2:$C$16,((ROW(H4)-3)*3)-2)
I4:K8I4=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)))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top