Lookup...find next

[Guest]

Hi,

Given Column A is a list of students and Column B the group they beling to
(i.e. A1, A2, A3, B1, B2, B2), I am trying to find a formula that will find
the "next student" in a given group.

For example, sheet1! has:
Sarah Alpha 1
Mike Bravo 2
John Alpha 2
Rick Alpha 1
....


I would like to have a formula for cell B2 (find next Alpha 1) so that if
cell A1 has the name Sarah, it will return the next Alpha 1 student. In
this case, should return "Rick"

Is this possible?

Many thanks

 

[T. Valko]

If there are only 2 students per group:

=LOOKUP(2,1/(group="Alpha 1"),name)

If there are more than 2 students per group then try this array formula**:

=INDEX(name,SMALL(IF(group="Alpha 1",ROW(name)-MIN(ROW(name))+1),2))

Where 2 = the instance you want.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Guest]

Thanks for the reply but could you please clarify what "group" and "name"
means.

I have between 11 and 12 students per group.

Thank you

 

[T. Valko]

Those are the ranges of your data

Group is the range of cells where you have the groups listed.
Name is the range of cells where you have the names listed.

Replace those with the actual range references like A1:A10 and B1:B10 (or
whatever) or just name your ranges and use those names as is.

If you have more than 2 students per group then you'll have to use the array
formula.

Are you wanting to list *all* the members of a specific group?

Biff

 

[Guest]

Understood, thanks a lot.

Those are the ranges of your data

Group is the range of cells where you have the groups listed.
Name is the range of cells where you have the names listed.

Replace those with the actual range references like A1:A10 and B1:B10 (or
whatever) or just name your ranges and use those names as is.

If you have more than 2 students per group then you'll have to use the array
formula.

Are you wanting to list *all* the members of a specific group?

Biff

 

[T. Valko]

You're welcome!

Biff

Understood, thanks a lot.