Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookups vs Match
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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! "Dave Ramage" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help With MATCH & OFFSET | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
How do i Match all COLUMNS? | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |