Help with an array formula (Dynamic List)

[PatrickO]

I am looking for a way to create a dynamic list depending on the input in adjacent cell.

Here is a basic set of data

In scope? Project name
Yes A
Yes B
No C
No D
Yes E


My desired output would be the following list:

Project name
A
B
E

I have a nagging feeling this can be (easily) achieved using an array, but after many failed attempts I have had no sucess.

Thanks in advance.

 

[Matty]

Hi,

If you have Excel 2007 or later, assuming your list is in A1:B6, try:

=IFERROR(INDEX(B$2:B$6,SMALL(IF(A$2:A$6="Yes",ROW(A$2:A$6)-ROW(A$2)+1),ROWS(D$2:D2))),"")

If you have an older version of Excel, try:

=IF(ROWS(D$2:D2)<=COUNTIF(A$2:A$6,"Yes"),INDEX(B$2:B$6,SMALL(IF(A$2:A$6="Yes",ROW(A$2:A$6)-ROW(A$2)+1),ROWS(D$2:D2))),"")

Both these formulas require array entry, i.e. with CTRL+SHIFT+ENTER.

Matty

 

[PatrickO]

Worked like a charm, thanks for the quick reply.

 

[Matty]

Worked like a charm, thanks for the quick reply.

Good stuff. Glad to help.

Matty

 

[T. Valko]

I am looking for a way to create a dynamic list depending on the input in adjacent cell.

Here is a basic set of data

In scope? Project name
Yes A
Yes B
No C
No D
Yes E


My desired output would be the following list:

Project name
A
B
E

I have a nagging feeling this can be (easily) achieved using an array, but after many failed attempts I have had no sucess.

Thanks in advance.

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3