Excel

[Salma21]

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. 

[#2]

@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)))