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 https://www.youtube.com/watch?v=fDB1Ktyhp3Y&t=516s
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.
4 Replies
- 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,"")
- 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 - It returns #CALC!
I would also rather have it by single column instead of multiple e.g., B2:C7
Please advise
