Need help with VLOOKUP (I think)

[masseyhemenway]

Hello,
I have a spreadsheet that tracks attendance for classes. What I am attempting to do is create a different spreadsheet that will serve as an absent report. The Main Spreadsheet has a column that lists an "X" for two or more absences so I was going to use that criteria for listing the student on the absent report. On the report will be the student name, class, teacher. I've tried using VLOOKUP but so far have not been able to get it to work. Any help would be greatly appreciated. Thanks.

 

[kweaver]

Welcome to MrExcel.

You didn't explain how the Class sheet is structured. I made an assumption that row 1 has headers for the columns.
Then, column A has the student name, B has the class, C has the teacher, and D has the X or a blank.

Then, on the Attendance sheet in A2 (assumed headings again), I entered:

=IFERROR(INDEX(Class!A$2:A$10,SMALL(IF((Class!$D$2:$D$10="X"),MATCH(ROW(Class!$D$2:$D$10),ROW($D$2:$D$10)),""),ROWS($A$2:A2))),"")

and filled that down for however many students there are and then across the columns. Change my "10" to however many rows are in the Class sheet.

See if that does the trick.

 

[kweaver]

Minor changes:

=IFERROR(INDEX(Class!A:A,SMALL(IF(Class!$D:$D="X",MATCH(ROW(Class!$D:$D),ROW($D:$D)),""),ROWS($A$2:$A2))),"")

 

[masseyhemenway]

Oh wow.....that's a heck of a formula. I'm sorry I did not provide you with the proper information. I am sure you get tired of hearing this, but I have absolutely no excel training and what I've put together has been with a combination of 1- excel tutorials, 2- trial and error, 3- stubborn persistence, and 4- sheer luck. Having said that, if I may, there is a copy of the spreadsheet attached. Attendance is the main sheet, with Excessive Absences the second sheet. What I'd like to do is have individuals with 2 or more absences for the month automatically listed on the Excessive Absences sheet. I really appreciate your help and obviously, there is only so much one can do with tutorials. I look forward to hearing from you. Thanks.

 

[masseyhemenway]

So, it seems I spoke too soon. I can't seem to upload anything.

 

[kweaver]

Use XL2BB to provide your data.