List values by Criteria

[mucah!t]

Hello All,

I'd like the names from column C, adjacent to the word "new" in column A, listed in column E.

Any ideas?

Excel Workbook
	B	C	D	E
3	New	Alex		Alex
4	45	Tue		John
5	8	Wed		Matthew
6	New	John		Peter
7	7	Thu
8	65	Fri
9	65	Sat
10	New	Matthew
11	12	Wed
12	45	Thu
13	69	Fri
14	74	Sat
15	54	Sun
16	New	Peter
17	35	Sat
18	54	Sun
Sheet1

Excel 2003

 

[NBVC]

In D3 enter:

=COUNTIF(B3:B18,"New")

Then in E3:

=IF(ROWS($A$1:$A1)>$D$3,"",INDEX($C$3:$C$18,SMALL(IF($B$3:$B$18="New",ROW($B$3:$B$18)-ROW($B$3)+1),ROWS($A$1:$A$1))))

adjust ranges to suit and confirm with CTRL+SHIFT+ENTER and copy down

 

[mucah!t]

Hello Nbvc,

It only repeats the first name 4 times [Alex]...?

 

[NBVC]

Sorry, my bad:

 =IF(ROWS($A$1:$A1)>$D$3,"",INDEX($C$3:$C$18,SMALL(IF($B$3:$B$18="New",ROW($B$3:$B$18)-ROW($B$3)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER and copied down...

 

[mucah!t]

This one works just fine.
Thank you!