Forum Discussion
Excel
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
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)))
