Multiple Matched values Formula help needed

[seekerarcane]

I want to return the multiple matched values from Coloumn A, based on 2 lookup values "1", or "All" from Column F, ranged F3:F9.


Used this formula, but Its not returning the correct results in cell range A16:A20 in provided image here.


Imgur: The most awesome images on the Internet


Following array formula has been used, but need some fix;


=IFERROR(INDEX(A:A,SMALL(IF(OR($F$3:$F$9=1,$F$3:$F$9="All"),ROW($F$3:$F$9)),ROW(A1))),"")



Please look into it and revert with correction. Thanks

 

[Nishant94]

Excel 2016
	A	B	C	D	E	F
1	CATEGORY	HOURS	TRAINING TITLE	REFERENCE	FREQUENCY	2017
2	Healty & Safety
3	Health & Safety	2	HS 1:Asbesots Training	NKFR-Training	Annual	1
4	Health & Safety	2	HS 2: Bloodborne Pathogens	NKFR-Training	Annual	1
5	Health & Safety	1	HS 4: Hearing Loss Prevention	NKFR-Training	Annual	2
6	Health & Safety	1	HS 5: Heat And Cold Stress	NKFR-Training	Annual	3
7	Health & Safety	2	HS 6: Medical Protection(Infection)	NKFR-Training	Annual	3
8	Health & Safety	2	HS 7:Personal Protective Equipment	NKFR-Training	Annual	4
9	Health & Safety	1	HS 8:Safety Meeting	NKFR-Training	Quaterly	All
10
11
12
13	CATEGORY
14	Health & Safety
15	Health & Safety
16	Health & Safety
17
18
19
Sheet6

Cell Formulas
Range	Formula
A14	{=IFERROR(INDEX($A$3:$A$9,SMALL(IF(($F$3:$F$9=1)+($F$3:$F$9="All"),ROW($F$3:$F$9)-ROW($F$3)+1),ROWS(A$14:A14))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nishant94]

In case if the category remains same and you just want to populate the category the number of times it meets the criteria then you can use this:

Excel 2016
	A	B	C	D	E	F
1	CATEGORY	HOURS	TRAINING TITLE	REFERENCE	FREQUENCY	2017
2	Healty & Safety
3	Health & Safety	2	HS 1:Asbesots Training	NKFR-Training	Annual	1
4	Health & Safety	2	HS 2: Bloodborne Pathogens	NKFR-Training	Annual	1
5	Health & Safety	1	HS 4: Hearing Loss Prevention	NKFR-Training	Annual	2
6	Health & Safety	1	HS 5: Heat And Cold Stress	NKFR-Training	Annual	3
7	Health & Safety	2	HS 6: Medical Protection(Infection)	NKFR-Training	Annual	3
8	Health & Safety	2	HS 7:Personal Protective Equipment	NKFR-Training	Annual	4
9	Health & Safety	1	HS 8:Safety Meeting	NKFR-Training	Quaterly	All
10
11
12
13	CATEGORY
14	Health & Safety
15	Health & Safety
16	Health & Safety
17
18
19
Sheet6

Cell Formulas
Range	Formula
A14	=IF(ROWS(A$14:A14)>SUMPRODUCT(COUNTIF($F$3:$F$9,{1,"All"})),"","Health & Safety")

 

[seekerarcane]

Thank you very much Nishant Ghosh