Index a table for multiple entries and two specific entries

[Kotta]

Hi all, I have the below data and I would like to create a list, in a separate worksheet, that will return all the fruits that have less than -4 value in the second column and all the fruits that have values over 5 in the second column, as well as kiwi and strawberry no matter what the values are in the second column. How would I do that? In actually have thousands of rows to shift through but this is an example to help with the formula. Any help would be highly appreciated! Thank you in advance.

apple	-5​
orange	5​
banana	-4​
kiwi	3​
strawberry	1​
apple	-6​
orange	6​

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D
1
2	apple	-5		apple
3	orange	5		kiwi
4	banana	-4		strawberry
5	kiwi	3		apple
6	strawberry	1		orange
7	apple	-6
8	orange	6
9
Data

Cell Formulas
Range	Formula
D2:D6	D2	=FILTER(A2:A100,(B2:B100<-4)+(B2:B100>5)+(A2:A100="kiwi")+(A2:A100="strawberry"))
Dynamic array formulas.

 

[Kotta]

Hi Fluff,
Thank you so much for the response! I think this is very helpful. Is it possible to also add a criteria for kiwi? For example return kiwi if column B is positive?
Thank you again for your help!

 

[Fluff]

How about

=FILTER(A2:A100,(B2:B100<-4)+(B2:B100>5)+(A2:A100="strawberry")+((A2:A100="kiwi")*(B2:B100>0)))

 

[Kotta]

This is fantastic! Thank you so much for your help!! I really appreciate it!!

 

[Fluff]

You're welcome & thanks for the feedback.