Forum Discussion
Return Multiple Match Results
I'm trying to return multiple matches in the Calcs tab from the Data tab. Office 365.
I've followed this tutorial Return Multiple Match Results in Excel (2 methods) - YouTube
But I don't think I've got it quite right.
It's returning names that aren't classed as status open.
Formula used: =IF(ROWS($H$2:H2)<=$H$1,INDEX(Data!B:B,AGGREGATE(15,3,(Data!$E:$E=Calcs!$E$1)/(Data!$E:$E=Calcs!$E$1)*(ROW(Data!$E:$E)-ROW(Data!$E$1)),ROWS($H$2:H2))),"")
Data tab
| x | Last Name | First Name | x | Status |
| Seger | Bob | Open | ||
| Dylan | Bob | Open | ||
| Cash | Johnny | Open | ||
| Iver | Bon | Closed | ||
| Anthony | Oliver | Closed | ||
| Bryan | Zach | Open |
Calcs tab desired
Cell E1 is text: Open
Cell H1 is count of total open: 4
| Status | Last Name | First Name |
| Open | Seger | Bob |
| Open | Dylan | Bob |
| Open | Cash | Johnny |
| Open | DBryan | Zach |
Please advise.
- It returns #CALC!
I would also rather have it by single column instead of multiple e.g., B2:C7
Please advise - Also tried https://www.xelplus.com/return-multiple-match-values-in-excel/
=IFERROR(INDEX(Data!$B:$B,AGGREGATE(15,3,((Data!$E:$E=Calcs!$E$1)*ROW(Data!$B:$B))-ROW(Data!$B$1),ROWS(Calcs!$H$1:H2))),"")
Which returns blank cell - Managed to get it to work with https://www.excelcampus.com/functions/return-multiple-values-with-the-filter-function/ . =FILTER(Data!B:B,Data!$E:$E=Calcs!$G$2,"")
