Formula for Filling Data based on Criteria from a Drop Down

In my attached worksheet, I would like certain cells to populate with names based on criteria entered in a drop down menu.

For example, in my worksheet I have a list of names in Column B, I would like Cells H2 to H4 to populate if an X is selected from any of the names listed in Column C. I would also like it to list them in order based on the Rank in column A.

Now the final part is I would like the name to be removed from the queue list up top in column H if any text at all is entered in column D for that name.

In Sheet 2 I've listed what my worksheet should like like if correctly done. Thanks for any help on this!

Originally Posted by stunn

In H1 - array entered (press Ctrl+Shift+Enter instead of just enter):

=IFERROR(INDEX($B$5:$B$14,MATCH("X",$C$5:$C$14&$D$5:$D$14,0)),"-")

In H2 - also array entered:

=IFERROR(INDEX($B$5:$B$14,MATCH("X",IF(COUNTIF($H$2:H2,$B$5:$B$14)>0,"zz","")&$C$5:$C$14&$D$5:$D$14,0)),"-")

Copy H2 downward as far as required.

(Note: this assumes that the names will always be sorted by the ranking column).

Frickin awesome! Thanks very much