Create a sequential list of values that match criteria?

[Rhodes19]

I'm looking for a way to create a list of values that matches a certain criteria while skipping those values that don't match.

For example, in the attached image, let's say I want to create a list of the products that cost less than $5.00. A simple IF will return the correct products, but only in the corresponding row. What I'd like to do is essentially remove those rows that don't match altogether, and have the next matching value moved up, like in the column named 'Desired Outcome.'

Any help would be appreciated! Thanks!

 

[lrobbo314]

Book1.xlsx
	B	C	D	E
3	Product	Cost		Outcome
4	Apple	4.75		Apple
5	Lettuce	3.25		Lettuce
6	Potato	3.75		Potato
7	Orane	5.5		Radish
8	Banana	5.25		Pear
9	Radish	4
10	Pear	4.5
Sheet2

Cell Formulas
Range	Formula
E4:E8	E4	=FILTER(B4:B10,C4:C10<5)
Dynamic array formulas.

 

[Rhodes19]

Amazing, thank you so much!

 

[kincsem]

Do the ranges have to be fixed or can you use a whole column?

 

[Fluff]

Best to avoid entire columns, but you could extend the ranges like

=FILTER(B4:B1000,(C4:C1000<5)*(B4:B1000<>""))