# Return Multiple Match Results

Copper Contributor

# 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.

4 Replies

# Re: Return Multiple Match Results

@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))``

# Re: Return Multiple Match Results

It returns #CALC!
I would also rather have it by single column instead of multiple e.g., B2:C7
Please advise

# Re: Return Multiple Match Results

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

# Re: Return Multiple Match Results

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