+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

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

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



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

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

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

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

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

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    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:

    =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...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

    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?

    =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))

    Thanks again!

  7. #7
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

    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!
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,246

    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: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

    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.

    Thank you for your help!
    Attached Files Attached Files
    Last edited by Dord25; 12-11-2017 at 04:06 PM.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,246

    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: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

    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. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,013

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

    Quote Originally Posted by Dord25 View Post
    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

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,246

    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. #14
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

    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. #15
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

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

    Quote Originally Posted by Glenn Kennedy View Post
    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))
    If I want to add another criteria within the match function, would it be INDEX('Math Grades Only'!$M$2:$M$6,MATCH(MIN(AND(IF('Math Grades Only'!$C$2:$C$15=A2,'Math Grades Only'!$H$2:$H$15), 'Math Grades Only'!$E$2:$E$15="119")),'Math Grades Only'!$N$2:$N$6,0))

    That resulted in an error. Thank you.

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,246

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

    See if writing it this way helps:
    Formula: copy to clipboard
    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. #17
    Registered User
    Join Date
    12-08-2017
    Location
    United States
    MS-Off Ver
    2013
    Posts
    40

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

    Quote Originally Posted by JeteMc View Post
    See if writing it this way helps:
    Formula: copy to clipboard
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 08-21-2019, 02:36 AM
  2. INDEX/MATCH with greater than or less than criteria
    By DZ217 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2018, 11:57 AM
  3. Replies: 11
    Last Post: 02-01-2016, 04:58 PM
  4. [SOLVED] Sum Index Match Dates Between Multiple Date Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-30-2014, 08:54 PM
  5. Index Match with Multiple Criteria One to Nearest Date
    By burningeagle in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-16-2014, 11:59 AM
  6. [SOLVED] Index Match equal to or less than date with multiple criteria
    By harrismlzn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2014, 05:38 PM
  7. [SOLVED] Index/Match - multiple criteria & greater than!
    By kennedy.clan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2013, 09:03 AM

Bookmarks

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