Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2273969%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273969%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Windows%2010%20enterprise%2C%20using%20EXCEL%20of%20Microsoft%20Office%20Professional%20Plus%202016.%3C%2FP%3E%3CP%3EI%20have%20two%20workbooks%20with%20the%20following%20data%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorkbook%201%3A%3C%2FP%3E%3CP%3EAdam%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BBlack%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20White%3C%2FP%3E%3CP%3EPaul%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BBlack%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Black%3C%2FP%3E%3CP%3ESusan%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20White%3C%2FP%3E%3CP%3EAdam%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20White%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorkbook%202%3A%3C%2FP%3E%3CP%3ESusan%3C%2FP%3E%3CP%3EAdam%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3CP%3EAdam%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20two%20workbooks%20have%20numerous%20entries%20as%20such.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20is%20to%20get%20the%20nth%20occurrence%20for%20the%20nth%20match%2C%20i.e.%20get%20the%20second%20column%20by%20order%20of%20occurrence%20corresponding%20to%20each%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

 

On Windows 10 enterprise, using EXCEL of Microsoft Office Professional Plus 2016.

I have two workbooks with the following data example:

 

Workbook 1:

Adam         Black

John          White

Paul           Black

John          Black

Susan        White

Adam        White

 

Workbook 2:

Susan

Adam

John

John

Paul

Adam

 

The two workbooks have numerous entries as such.

 

What I need is to get the nth occurrence for the nth match, i.e. get the second column by order of occurrence corresponding to each name.

 

Thank you. 

 

1 Reply

@Salma21 

Let's say the first list is in Sheet1, starting in A1.

In B1 on the second sheet, enter the following array formula confirmed with Ctrl+Shift+Enter:

 

=INDEX(Sheet1!$B$1:$B$6,SMALL(IF(Sheet1!$A$1:$A$6=A1,ROW(Sheet1!$A$1:$A$6)-ROW(Sheet1!$A$1)+1),COUNTIF(A$1:A1,A1)))

 

Adjust the sheet name and the ranges (I used A1:A6 and B1:B6 following your example), then fill down.

If your sheets are actually in different workbooks, add the workbook reference:

 

=INDEX('[OtherWorkbook.xls]Sheet1'!$B$1:$B$6,SMALL(IF('[OtherWorkbook.xls]Sheet1'!$A$1:$A$6=A1,ROW('[OtherWorkbook.xls]Sheet1'!$A$1:$A$6)-ROW('[OtherWorkbook.xls]Sheet1'!$A$1)+1),COUNTIF(A$1:A1,A1)))