generate a selective list
 

hi,
Please help on this:
I have list of two columns as shown below:
Rank Names
1 Josh
2 jason
3 darryl
2 mike
4 paul
2 aaron

On a different sheet I want to have a rank input cell, and generate a column
listing all the names corresponding to that rank.
for example
rank 2 (input cell,I enter this value)
output column:
jason
mike
aaron
If I use vlookup function I get the first name in the list (in this case
jason) three times,
how can achieve this?

thanks in advance,

generate a selective list
 

Assuming that rank is in A1, put this array formula in B1 and copy down as
far as is required

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$20=$A$1,ROW($A $1:$A$20),""),ROW(A1))),"",
INDEX(Sheet1!$B$1:$B$20,SMALL(IF(Sheet1!$A$1:$A$20 =$A$1,ROW($A$1:$A$20),""),ROW(A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Excel issue" <Excel wrote in message
...
hi,
Please help on this:
I have list of two columns as shown below:
Rank Names
1 Josh
2 jason
3 darryl
2 mike
4 paul
2 aaron

On a different sheet I want to have a rank input cell, and generate a
column
listing all the names corresponding to that rank.
for example
rank 2 (input cell,I enter this value)
output column:
jason
mike
aaron
If I use vlookup function I get the first name in the list (in this case
jason) three times,
how can achieve this?

thanks in advance,