|This can be done fairly easily with a macro and if you are willing to try it, I can suggest some code.|
If you want to do it strictly with formulae, it can be done, but there are a few extra steps.
Excel provides a number of lookup functions, but unfortunately, they all find the first occurrence of the value you are looking up within the given range. Assuming your last names are sorted in alphabetical order, you are going to have class designations spread out throughout the list.
Using a macro to loop through the list and match student names to a specific class designation is easy. However, to do it with a formula means that you have to create unique class designations for the lookup functions to find.
Here's one way to do it:
Let's say you have a list of last names in Column A and class designations in Column H. I'll use letters:
1 Last 1 A
2 Last 2 B
3 Last 3 C
4 Last 4 A
5 Last 5 C
6 Last 6 B
7 Last 7 C
8 Last 8 B
9 Last 9 A
Here's what you need to do:
Insert a column to the left of A, which will move your last names into B and class designations to I. (You can hide this new column A later if you want.)
Put this formula in A1 and drag it down:
=I1 & COUNTIF($I$1:I1,I1)
Note the use of the Absolute $I$1 and Relative I1 in the COUNTIF range. This is crucial.
What this will do is create class designations with a number representing each occurrence of the class, so your sheet now looks like this:
A B I
1 A1 Last 1 A
2 B1 Last 2 B
3 C1 Last 3 C
4 A2 Last 4 A
5 C2 Last 5 C
6 B2 Last 6 B
7 C3 Last 7 C
8 B3 Last 8 B
9 A3 Last 9 A
Now you have a list of unique names for the lookup function to find, but the students are still associated with their same class.
Next, in the Sheet where you want to list everyone in Class A, put this formula in Row 1 of the column where you want the last name and drag it down as far as you want.
=VLOOKUP("A" & ROW(),Sheet1!$A$1:$B$9,2,0)
The ROW() function will return 1, then 2, then 3, etc. as you drag it down and VLOOKUP will lookup A1, then A2, then A3, etc.
In rows 1 - 3, you should have:
which are the three students in Class A
In another sheet, change the "A" to a "B" and it will lookup B1, B2, B3, etc. returning:
Now, if you happen to drag the formula down too far (in this case past Row 3 since there are only 3 occurrences of each class) VLOOKUP will return #N/A since there is no A4 in the list. You can avoid this problem by expanding the formula to check for the #N/A error, returning "nothing" if there is an error: