Return list in a range, all values, based on condition

[Kanuck]

Hello,

I am trying to return the values in column B, based on a condition in column A. Below is the formula I have which is working, but returns values only once if for values that are duplicated. i.e. if 'apple' is the result on the sheet 5 times, the list will only return it once. I was to see it 5 times. I need it to return all values which meet the criteria when searching the range.
Should I be using a different function?

{=index('$B$3:$B$100, MATCH(0,IF($A$3:$A$100<>"Yes",countif($A$3:$A3,$B$3:$B$100),""),0))}

Thank you in advance.
Kanuck

 

[Toadstool]

Hi Kanuck,

I'm not sure I'm following but you could probably used the new FILTER function, whereas my old Excel 2016 would use SMALL or AGGREGATE to retrive all occurrences from one column based on another column.

Kanuck.xlsx
	A	B	C	D
1			Find all=	Apple
2	Fruit	Type		List
3	Orange	Navel		Granny Smith
4	Apple	Granny Smith		Blenheim
5	Banana	Cavendish		Fuji
6	Apple	Blenheim
7	Apple	Fuji
8	Banana	Plantain
9
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=IFERROR(INDEX($B$3:$B$9999,AGGREGATE(15,6,ROW($B$3:$B$9999)-ROW($B$2)/($A$3:$A$9999=$D$1),ROW()-ROW($D$2))),"")

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Return list in a range, all values based on condition
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Kanuck]

Thank you @Toadstool , exactly what was needed.
@Fluff thank you for letting me know, will do as outlined in future

 

[Fluff]

As Toadstool mentioned, you can also use the FILTER function.

+Fluff 1.xlsm
	A	B	C	D
1			Find all=	Apple
2	Fruit	Type		List
3	Orange	Navel		Granny Smith
4	Apple	Granny Smith		Blenheim
5	Banana	Cavendish		Fuji
6	Apple	Blenheim
7	Apple	Fuji
8	Banana	Plantain
9
Lists

Cell Formulas
Range	Formula
D3:D5	D3	=FILTER(B3:B8,A3:A8=D1)
Dynamic array formulas.