#1
|
||||
|
||||
Index Match Formula to return value from a list with criteria
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. |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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.) |
#4
|
||||
|
||||
Quote:
Finally, I got the formula right. Again, thank you. Last edited by Marcia; 10-26-2018 at 10:15 PM. |
#5
|
||||
|
||||
Quote:
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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
need Index, Match, Max to return SUMPRODUCT? | IRHSAM | Excel | 0 | 11-17-2017 09:48 AM |
Excel Index and Match function with multiple criteria not searching the next record | mushtaqkadar | Excel | 3 | 06-17-2017 12:20 AM |
Create a unique list of values that match a criteria, sorted in order of another criteria | BradRichardson | Excel | 2 | 01-03-2017 12:25 AM |
How to make MATCH/INDEX formula return multiple successive responses. | danigirl121 | Excel | 7 | 06-08-2016 01:57 PM |
Vlookup or Index/Match - Multiple Criteria | ruci1225 | Excel | 1 | 01-15-2012 07:31 AM |