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, 10 views)