Return Multiple Match Results

Copper Contributor

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

xLast NameFirst NamexStatus
 SegerBob Open
 DylanBob Open
 CashJohnny Open
 IverBon Closed
 AnthonyOliver Closed
 BryanZach Open

 

Calcs tab desired

Cell E1 is text: Open

Cell H1 is count of total open: 4

StatusLast NameFirst Name
OpenSegerBob
OpenDylanBob
OpenCashJohnny
OpenDBryanZach

 

Please advise. 

4 Replies

@Adam08780 If you are on Microsoft-365 then could use FILTER() function.

=HSTACK(FILTER(E2:E7,E2:E7=H1),FILTER(B2:C7,E2:E7=H1))

Harun24HR_0-1700621199252.png

 

 

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