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