# Excel

Occasional Visitor

# 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:

John          White

Paul           Black

John          Black

Susan        White

Workbook 2:

Susan

John

John

Paul

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.

# Re: Excel

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