Extracting contents from a table to build a new list based on other criteria in the table

[glad_ir]

Hi,

Please can I get some help.

I have a table in a sheet in cells B40:C60 that contains people's names in B40:B60 and a number between 0 and 1 associated with each name in C40:C60. I would like the sheet to extract any name in column B that has a number >0 associated with it in column C into a new list in column E starting at E5. I would like the new list in column E to update as the contents of B40:C60 change. I think one approach may be to use an array formula but my knowledge doesn't stretch that far!

Any help would be very much appreciated.

Thank you,
Iain

 

[Fluff]

How about

+Fluff 1.xlsm
	B	C
40	A	0
41	B	1
42	C	0
43	D	0
44	E	0
45	F	1
46	G	1
47	H	1
48	I	1
49	J	1
50	K	0
51	L	0
52	M	1
53	N	1
54	O	0
55	P	1
56	Q	1
57	R	0
58	S	1
59	T	1
60	U	1
Master

+Fluff 1.xlsm
	E
1
2
3
4
5	B
6	F
7	G
8	H
9	I
10	J
11	M
12	N
13	P
14	Q
15	S
16	T
17	U
18
19
20
21
Master

Cell Formulas
Range	Formula
E5:E19	E5	=IFERROR(INDEX($B$40:$B$60,AGGREGATE(15,6,(ROW($B$40:$B$60)-ROW($B$40)+1)/($C$40:$C$60>0), ROWS(E$5:E5))),"")

 

[glad_ir]

That works like a charm! Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.