Lookup...find next
 

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

Lookup...find next
 

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

"Maxime Maugeais" wrote in
message ...
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

Lookup...find next
 

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" wrote:

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

"Maxime Maugeais" wrote in
message ...
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

Lookup...find next
 

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

"Maxime Maugeais" wrote in
message ...
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" wrote:

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

"Maxime Maugeais" wrote in
message ...
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

Lookup...find next
 

Understood, thanks a lot.

"T. Valko" wrote:

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

"Maxime Maugeais" wrote in
message ...
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" wrote:

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

"Maxime Maugeais" wrote in
message ...
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

Lookup...find next
 

You're welcome!

Biff

"Maxime Maugeais" wrote in
message ...
Understood, thanks a lot.

"T. Valko" wrote:

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

"Maxime Maugeais" wrote in
message ...
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" wrote:

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

"Maxime Maugeais" wrote in
message ...
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