Generate a list from array based on two conditions

[areemjee]

Hi guys, I am trying to generate a list (from cell J5 and below) based on an employee's role (cell J2) and whether there is a value in any of the KPI components associated to that role.

This is the function that I currently have in cells J5:J15. I cannot figure out a way to look up the column based on the role in cell J2.

Any help would be appreciated. TIA

=IFERROR((INDEX($A$5:$A$15,SMALL(IF($B$5:$B$15>0,ROW($A$5:$A$15)-ROW($A$5)+1),ROWS($B$5:B5)))),"")

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2									Role NO.	Role2
3
4	KPI Component	Role1	Role2	Role3	Role4	Role5	Role6			Points	Modified Formula
5	KPI1	5%								KPI1	KPI2
6	KPI2		5%		5%		5%			KPI3	KPI3
7	KPI3	5%	5%		5%	5%	5%			KPI4	KPI4
8	KPI4	5%	5%	5%		5%	5%			KPI5	KPI7
9	KPI5	5%		5%	5%		5%				KPI8
10	KPI6				5%		5%
11	KPI7		5%			5%	5%
12	KPI8		5%		5%	5%
13	KPI9			5%	5%	5%
14	KPI10			5%		5%
15	KPI11				5%
16	KPI12				5%
17
Sheet1

Cell Formulas
Range	Formula
J5:J16	J5	=IFERROR((INDEX($A$5:$A$15,SMALL(IF($B$5:$B$15>0,ROW($A$5:$A$15)-ROW($A$5)+1),ROWS($B$5:B5)))),"")
K5:K16	K5	=IFERROR((INDEX($A$5:$A$15,SMALL(IF(OFFSET($B$5,0,MATCH($J$2,$B$4:$G$4,0)-1,11,1)>0,ROW($A$5:$A$15)-ROW($A$5)+1),ROWS($B$5:B5)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[areemjee]

Welcome to MrExcel Message Board.
Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2									Role NO.	Role2
3
4	KPI Component	Role1	Role2	Role3	Role4	Role5	Role6			Points	Modified Formula
5	KPI1	5%								KPI1	KPI2
6	KPI2		5%		5%		5%			KPI3	KPI3
7	KPI3	5%	5%		5%	5%	5%			KPI4	KPI4
8	KPI4	5%	5%	5%		5%	5%			KPI5	KPI7
9	KPI5	5%		5%	5%		5%				KPI8
10	KPI6				5%		5%
11	KPI7		5%			5%	5%
12	KPI8		5%		5%	5%
13	KPI9			5%	5%	5%
14	KPI10			5%		5%
15	KPI11				5%
16	KPI12				5%
17
Sheet1

Cell Formulas
Range	Formula
J5:J16	J5	=IFERROR((INDEX($A$5:$A$15,SMALL(IF($B$5:$B$15>0,ROW($A$5:$A$15)-ROW($A$5)+1),ROWS($B$5:B5)))),"")
K5:K16	K5	=IFERROR((INDEX($A$5:$A$15,SMALL(IF(OFFSET($B$5,0,MATCH($J$2,$B$4:$G$4,0)-1,11,1)>0,ROW($A$5:$A$15)-ROW($A$5)+1),ROWS($B$5:B5)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Works perfectly, thank you

 

[maabadi]

You're Welcome & Thanks for Feedback.