Here's one way:
=INDEX(Attendees,MIN(IF(Class_Numbers=1,1,65536)*I F(COUNTIF
(D$1:D1,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$1:D1 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)*I F(COUNTIF
(D$1:D1,Attendees)=0,1,65536)*ROW(Class_Numbers)) + 11,1)
Cheers,
Dave
-----Original Message-----
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
.
|