During or within a school year, some learners transfer in, transfer out or drop out from school. I have used index match functions to extract those that either transferred or dropped out during the whole school term but I want to extract the learners' movements every month starting June 2018. I tried the nesting the IF function but gave up after several hours.


Attached is the data I am working on and I need the following formula to:

1. Extract the Remarks (LE, T/I, T/O, DRP) from the tDATA into column AM of SF JUNE sheet, of each enrolled learner during the month of June. It should not return Remarks "T/O" or "DRP" that happened after June 30, or the end of the reporting period (cell AC6).

2. In the SF JULY sheet, what's the formula in Column B to extract the names of learners excluding those who transferred out or dropped out in June.

Attached Files

JUNE 2018 QUERY.xlsm (113.7 KB, 8 views)

Do these work?


in AM16 of June sheet:


=IFERROR(INDEX(REMARKS,MATCH(1,INDEX((FULL_NAME=$B 16)*(tDATA[DATE OF TRANSFER OUT/DROP OUT]<=$AC$6),0),0)),"")


copied down


In B16 of July sheet:


=IFERROR(INDEX(FULL_NAME,MATCH(SMALL(IF((COUNTIF($ B$15:$B15,FULL_NAME)=0)*(GRADE=$AC$8)*(SECTION=$AI $8)*(DATE_ENROLLED<=AC$6)*((tDATA[DATE OF TRANSFER OUT/DROP OUT]="")+(tDATA[DATE OF TRANSFER OUT/DROP OUT]>EOMONTH(AC$6,-1)))*(SEX="M"),COUNTIF(FULL_NAME,"<"&FULL_NAME),"" ),1),COUNTIF(FULL_NAME,"<"&FULL_NAME),0)),"")


confirmed with CTRL+SHIFT+ENTER and copied down

I modified your workbook a bit - look for attached file.

To create a workbook for specific conditions (like specific month) is not very productive. Try design the workbook in a way, which allows to work with single workbook with smallest possible number of sheets for data entry, and with single report sheet for same kind of reports. When you do this, you design a workbook once, and may use it for long time without any changes to design. As example I designed a Report sheet based on your Data sheet (I made some changes there btw.)

Attached Files

QUERY.xlsm (119.9 KB, 8 views)

Thank you for the immediate reply on my questions. The formula in AM16 of June sheet worked like a charm but when I copied it to AM16 of July sheet, the "LE" and "T/I" Remarks in June still appear in July. Sorry, I missed emntioning this on my original query. Only the Remarks occurring during the month should be reflected in the reporting month. The starting date of the month is found in E10, the end date AC6. I added another column in tDATA named DATE_REMARKS, this range contains the date/s of the Remarks. I'm at a loss on how to integrate the start date, end date and DATE_REMARKS in the formula you gave. *(tDATA[DATE_REMARKS]>=$E$10)*(tDATA[DATE_REMARKS<=$AC$6) doesn't work.


Finally, I got the formula right. Again, thank you.

Thank you for the reply. Yeah, I've applied your method of assigning Row Nos and using these as unique identifiers in other databases that our office handles in our little hometown. Although I have yet to work out how I will use these Row Nos into the required/official forms.