Make list from lookup/if without gaps between hits

[horsefish01]

I have a list of names from B2:B300 IN C2:C300 I put an "S" if that person makes a sale.

In A:A i want a list of only the people that has a sale. But how do I get them to overlook gaps.

If 5 people from the list of 300, I want those names in A2:A7 etc.

[sourabhg98]

Use this formula in A2-

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
copy down....

Column A Formula	Column A result	Column C	Column D
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A1))),"")	Sourabh	Sourabh	S
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A2))),"")	Jack	Sachin
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A3))),"")	Mathews	Hansemand
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A4))),"")	Mike	Jack	S
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A5))),"")		Albert
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A6))),"")		Robert
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A7))),"")		Mathews	S
=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A8))),"")		Mike	S

EDIT-FORMULA CORRECTED

[JohnTopley]

... should be

=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A1))),"")

[sourabhg98]

... should be

=IFERROR(INDEX(B:B,SMALL(INDEX(IF($C$2:$C$300="S",ROW($C$2:$C$300),""),0),ROW(A1))),"")

oops sorry for that error...

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Name	Name	Status
2	Connie	Bill
3	Tom
4	Tracy
5		Connie	S
6		Tom	S
7
8		Pete
9		Tracy	S
10

This array formula** entered in A2:

=IFERROR(INDEX(B:B,SMALL(IF(C$2:C$300="S",ROW(C$2:C$300)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.