Lookups vs Match

[Bob Alford]

Hello! I am having trouble trying to create class rosters in a worksheet
using class numbers and attendee names from a different worksheet. My
attendee sheet contains attendee names in column G and their requested class
number in column H. Obviously (and hopefully) multiple attendees will
request the same class numbers so I expect multiple matches on class number.
Because of multiple sessions some attendees will not make a class request and
thus will have no value in class number. I have used the following formula
and copied it into all of the available seats for the class.
=VLOOKUP(A1,'2005 Attendees'!G2:H147,2,FALSE) What I get is the first match
repeated throughout the entire roster. From what I have been reading, this
is expected when using VLOOKUP.

My data looks like this in the Attendees sheet

Class number Attendee

1 Alford, Chuck
Evans, Jim
7 Jones, Frank
1 Pratt, Charlie
12 Smith, Bob
Sotich, Beth
1 Wiggins, Sherry

I have the class numbers for each classes roster entered in cells (A1 in
this example) in the Roster sheet. What I am hoping to get in the Roster for
Class Number 1 is:
Alford, Chuck
Pratt, Charlie
Wiggins, Sherry

What I actually get is:
Alford, Chuck
Alford, Chuck
Alford, Chuck

Suggestions will be very much appreciated!
Thanks

 

[Dave Ramage]

Here's one way:

=INDEX(Attendees,MIN(IF(Class_Numbers=1,1,65536)*IF(COUNTIF
(D$11,Attendees)=0,1,65536)*ROW(Class_Numbers)),1)

This is an array formula, so hold Ctrl + Shift as you
enter it.

As written, this formula must be entered into cell D2,
then dragged down until you get a #REF! error. If it isn't
in D2, then substitute D$11 for the cell above the first
cell that you enter the formula in. E.g. if formula is
entered in Q5 then enter Q$4:Q4 here.

This formula also assumes that your Attendees list starts
in row 1. If it doesn't, then add or subtract an
appropriate offset into the second parameter of the INDEX
function. E.g. if your range for Attendees is A12:A100
then the formula will be:
=INDEX(Attendees,MIN(IF(Class_Numbers=1,1,65536)*IF(COUNTIF
(D$11,Attendees)=0,1,65536)*ROW(Class_Numbers)) + 11,1)

Cheers,
Dave

 

[Bob Alford]

Dave, thanks for the suggestion. I am still trying to transcribe your
function to my spreadsheets and will let you know my results. I am having a
bit of a problem since Attendees is contained in a range of cells on another
sheet and Class_Numbers is also contained in a range of cells on another
sheet. I will work on adjusting my "Thinking Cap" and let you know what
happens. Again, Thanks!