Index Match Formula returning more than just correct value

[kmsprague]

Hi there,
I'm using a formula with Index and Match (Excel 365) in an attempt to return all lines that contain certain the Company1 name. The formula works and it brings back the lines I need, but it also brings back other lines afterwards that don't match (see highlighted example below). Basically, I need it to stop populating information once the information from Company1 has all been brought into Sheet 2 so that I'm only capturing info from that company. Any help/ideas are greatly appreciated!! Thank you so much!

Here's my current formula: =INDEX('Sheet1'!$A2:$G$5,MATCH('Sheet2'!$B$2,'Sheet1'!$B2:$B5,0),0)

Sheet 1 with Info:

What's being returned in Sheet 2:

 

[AhoyNC]

Since you have Excel 365, try the FILTER function.

Sheet 1

Book2
	A	B	C	D	E	F	G
1	Account	Companyl	Departmentl	Location	Company2	Department2	Sum of Total
2	123456	ABCD	001	002	EFGH	123	5
3	123456	ABCD	001	002	EFGH	123	10
4	123456	ABCD	001	002	EFGH	123	1.5
5	123456	LMNO	001	002	EFGH	123	20
Sheet1

Sheet 2

Book2
	A	B	C	D	E	F	G	H	I	J
1	Date	Company	Account Type	Account Number	Company	Dept.	Location	Owning Company	Owning Dept.	Total
2	11/14/2022	ABCD	Ledger	123456	ABCD	001	002	EFGH	123	5
3	11/14/2022	ABCD	Ledger	123456	ABCD	001	002	EFGH	123	10
4	11/14/2022	ABCD	Ledger	123456	ABCD	001	002	EFGH	123	1.5
Sheet2

Cell Formulas
Range	Formula
D2:J4	D2	=FILTER(Sheet1!$A$2:$G$5,Sheet1!$B$2:$B$5=Sheet2!$B$2,"No Match")
Dynamic array formulas.

 

[kmsprague]

AhoyNC, thank you SO much! This worked perfectly!

 

[AhoyNC]

You're welcome thanks for the feedback.