Forum Discussion
Salma21
Apr 15, 2021Copper Contributor
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 ...
HansVogelaar
Apr 15, 2021MVP
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)))