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


Please advise. 

4 Replies

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





It returns #CALC!
I would also rather have it by single column instead of multiple e.g., B2:C7
Please advise
Also tried
Which returns blank cell
Managed to get it to work with . =FILTER(Data!B:B,Data!$E:$E=Calcs!$G$2,"")