Index match with multiple criteria, with one criteria being greater than a certain date

part 1,an array formula:

=IFERROR(INDEX('Math Grades Only'!$F$2:$F$1537,MATCH(1,('Math Grades Only'!$C$2:$C$1537=Cohort!A1306)*('Math Grades Only'!$A$2:$A$1537>=Cohort!D1306),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

With regard to the second part, can you repost a SMALL sample sheet (10-20 rows MAXIMUM) to facilitate checking of solutions.

here is the file, with Part 1 in place...

Forget about the sample sheet. However, please list ALL possible grades in DESCENDING order...

one way,another array formula, on the attached cut-down version of your monster sheet:

=INDEX('Math Grades Only'!$M$2:$M$6,MATCH(MIN(IF('Math Grades Only'!$C$2:$C$15=A2,'Math Grades Only'!$H$2:$H$15)),'Math Grades Only'!$N$2:$N$6,0))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Hello Dord25 and Welcome to Excel Forum.
If I understand then it appears this could be accomplished as follows:
1) Populate the sequence column of the 'Credits Earned by Semester' sheet using: =COUNTIFS(B$2:B2,B2)
2) Populate columns F:G (etc.) of the 'Full Student List' sheet using the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hi JeteMc and thank you. If I understand your formula right, it sums all credits if the sequence is 1 (first semester to appear) and the ID is the one in question. However, the sequence number may change depending on the first semester of the individual. I have attached a file that actually has cases.
For example, ID number 23111313 completed the program in 2/1/2015, but has credit data for the following terms:
09/01/14
09/01/15
02/01/16
09/01/16
02/01/17

Using the above Index formula provided by Glenn, I can choose the first semester after the person completed the program,
which would be 09/01/15 (Sequence #2), not 09/01/14 (Sequence #1). For the credits completed for the second semester after the program I would choose 02/01/16 (Sequence #3), and so on for the third and fourth semester. But there may be IDs that have more semesters of credit data that appear before the program completion date, so that the first correct term might appear as the third or fourth date in order, etc.
ID 12099368 completed the program in 2/1/2016 but has credit data for the following terms:
09/01/14
02/01/15
09/01/16
02/01/17

I would want to select the first semester credit data as 09/01/16, the second semester as 02/01/17, and the third and fourth would be blank.

Thank you for your help!

The following seems to be providing the correct results:
1) A helper column (Prog. date) which may be moved and/or hidden for aesthetic purposes is added to the 'Credits Earned by Semester' sheet, and is populated by the formula: =INDEX('Full Student List'!C$2:C$1338,MATCH(B2,'Full Student List'!A$2:A$1338,0))
2) The formula that populates the sequence column of the 'Credits Earned by Semester' sheet is: =COUNTIFS(B$2:B2,B2,A$2:A2,">"&E2)
3) Columns E:H on the 'Full Student List' sheet are populated using the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Originally Posted by Dord25

Thank you for the help--I greatly appreciate it. My only question is why do you use Column(A:A), Column(B:B), etc. to represent numbers rather than the number itself? Is there any benefit to doing so?

Hi Dord25.

JeteMc appears to offline at the moment.

Yes there is an advantage. It saves having to manually type each Sequence number 1,2,3,4 ...

By simply filling the formula across the numbers increment. If you continue across it will return credits clear up to the current maximum Sequence 11.

@Dave, thank you for your assistance.
@Dord25, You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

See if writing it this way helps:

Formula:

Please Login or Register  to view this content.

Remember to activate by simultaneously pressing the Ctrl, Shift and Enter keys before attempting to copy.
Let us know if you have any questions.