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

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

Hello,

I would like to use Index Match to retrieve a math grade that appears after a certain date a student was enrolled, picking the first one, but also basing it on ID.
I tried =INDEX('Math Grades Only'!E:E, MATCH(Cohort!A2&">=Cohort!D2", 'Math Grades Only'!C:C&'Math Grades Only'!A:A, 0)), but that was just a guess. I have seen documentation online for Index Match with multiple criteria and using greater than separately, but not together.

I also have a list of math grades and would like to choose the best grade for a course for the student. So if the student got a F, F, and then a B in Math 101, to choose the B. Is this possible in Excel? It would also require multiple criteria.

Any help is appreciated,
Dord25

2. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

part 1,an array formula:

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...

3. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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...

5. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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

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...

6. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

The grades would be A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D-, and F. However, I think I understand how to do it if it is the same as the smaller version you made.
I greatly appreciate the help and the worksheet you offered . Do you know why the below formula requires it to be an array? Is it because the IF statement creates an array of numbers from which the minimum is chosen from?

Thanks again!

7. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

Also, I don't know if I should post it here, since it involves the same project. I want to choose the credits earned based on an ID and date that is after the first term enrolled. Using your formula that would be:
=IFERROR(INDEX('Credits Earned by Semester'!C:C, MATCH(1, ('Credits Earned by Semester'!B:B='Full Student List'!A2)*('Credits Earned by Semester'!A:A>='Full Student List'!E2), 0)), "")

That would choose the credits earned in the earliest term after the date. However, I want to look at credits earned in their second semester, third semester, etc. Normally I would choose the credit data that appears second in the sequence as the second semester. This may be the second semester that appears in order, but it may not be the second semester that appears after the start term I am looking for. Is there a way to choose this?
I attached a smaller dataset, though I don't know if it has cases that match.

Thanks again!

8. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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.

9. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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.

10. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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.

11. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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?

12. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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.

13. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

@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.

14. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

Thank you both for the help. I hope to learn a lot from this forum!

15. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

Originally Posted by Glenn Kennedy
one way,another array formula, on the attached cut-down version of your monster sheet:

That resulted in an error. Thank you.

16. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

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.

17. ## Re: Index match with multiple criteria, with one criteria being greater than a certain dat

Originally Posted by JeteMc
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.
Thank you again. That worked!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1