| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Column Number via Lookup | Excel Worksheet Functions | |||
| Can't find the min of a lookup reference | Excel Worksheet Functions | |||
| Lookup Value and find Corresponding Value on another row same column | Excel Discussion (Misc queries) | |||
| Find and replace with a lookup | Excel Worksheet Functions | |||
| Can't find the right lookup formula for this | Excel Worksheet Functions | |||
Linear Mode
