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,

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,