Filtering out returned empty formula cells

[Hapakaya]

I need some help filtering out cells that a formula returns as "". I want to organize my cells so that cells that a formula returns data to is at the top and there are no empty "" cells above.
The following is how my sheet looks:

and I am using match/index formulas to return the values I want.

My question is how to take the Model# I have found at the bottom right and organize them so they are at the top with no empty cells above.

Thanks

 

[Hapakaya]

more context:

 

[Eric W]

Welcome to the MrExcel forum!

I think I get the gist of what you're doing, with the additional context. (You might consider looking into the XL2BB tool in this forum. It allows you to copy part of your sheet to the forum in a way that's easy for the helpers here to copy and work with. It's easy to download, install, and use. Look at the link in the reply box.)

Since you have Excel 365, this type of problem is ideally suited to the new FILTER function. Here's an example:

Book1
	AX	AY	AZ	BA	BB	BC
1	Manufacturer	Value1	Value2	Manufacturer	Value1	Value2
2	Ace	1	a	Trane	4	d
3	Ace	2	b		5	e
4	Carrier	3	c		6	f
5	Trane	4	d		7	g
6	Trane	5	e		9	i
7	Trane	6	f
8	Trane	7	g
9	Something else	8	h
10	Trane	9	i
11	Xteam	10	j
12	Xteam	11	k
13
Sheet1

Cell Formulas
Range	Formula
BB2:BC6	BB2	=FILTER(AY2:AZ12,AX2:AX12=BA2)
Dynamic array formulas.

You give it the range, the filter condition, and it creates a list of matching entries. See if it works for your case. There are ways to make it do some more complicated things too if your sheet requires it.

 

[Hapakaya]

Welcome to the MrExcel forum!

I think I get the gist of what you're doing, with the additional context. (You might consider looking into the XL2BB tool in this forum. It allows you to copy part of your sheet to the forum in a way that's easy for the helpers here to copy and work with. It's easy to download, install, and use. Look at the link in the reply box.)

Since you have Excel 365, this type of problem is ideally suited to the new FILTER function. Here's an example:

Book1
	AX	AY	AZ	BA	BB	BC
1	Manufacturer	Value1	Value2	Manufacturer	Value1	Value2
2	Ace	1	a	Trane	4	d
3	Ace	2	b		5	e
4	Carrier	3	c		6	f
5	Trane	4	d		7	g
6	Trane	5	e		9	i
7	Trane	6	f
8	Trane	7	g
9	Something else	8	h
10	Trane	9	i
11	Xteam	10	j
12	Xteam	11	k
13
Sheet1

Cell Formulas
Range	Formula
BB2:BC6	BB2	=FILTER(AY2:AZ12,AX2:AX12=BA2)
Dynamic array formulas.

You give it the range, the filter condition, and it creates a list of matching entries. See if it works for your case. There are ways to make it do some more complicated things too if your sheet requires it.

Yup filter ended up working out, I used this filter to get multiple criteria in to filter out the info.
=FILTER(E3:E289,(D3:D289=$BG$3)*(C3:C289=$BH$3))
Thanks for the help

 

[Eric W]

Happy to help! Thanks for the feedback. ?