+ Reply to Thread
Results 1 to 6 of 6

Conditional Multiple Criteria Lookup to Find Rows to Keep

  1. #1
    Registered User
    Join Date
    11-09-2018
    Location
    Earth
    MS-Off Ver
    2013 / 2016
    Posts
    3

    Question Conditional Multiple Criteria Lookup to Find Rows to Keep

    Dear fellow Excel Power Users:


    I have spent over 9 hours today trying to solve this myself, and must conclude that I cannot do this alone. I would be greatly appreciative of anyone who can help me to resolve this issue.

    I have a file (SEE ATTACHMENT example) in which I need to define which rows to discard and which to keep (which will eventually be automated with VBA).
    • Each row represents a question answered in a test
    • A user may have completed the test more than once
    • Each "attempt" at the test is represented by the "Unique AID" number (or UAID)

    My requirement / challenge is to find the appropriate UAID to keep, considering the following logic:
    1. Return the UAID of the highest "Overall Score" of any attempts completed in the last 12 months,
    2. ELSE, Return the UAID of the most recent attempt
    3. Mark each row as "Keep" if returned via above logic, otherwise mark as "Delete"

    Thanks again for any assistance you can provide!!!!!

    Attached Files Attached Files
    Last edited by akblack; 11-09-2018 at 01:47 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Multiple Criteria Lookup to Find Rows to Keep

    Hello and welcome to the forum.

    Try this in E2:

    =INDEX(UAID,MATCH(A2&MAX(IF((UserNr=A2)*(EndDate>=EDATE(TODAY(),-12)),Score)),UserNr&Score,0)) Ctrl Shift Enter

    Note that it would be more efficient to put =TODAY() in another cell then refer to that cell in the formula.

  3. #3
    Registered User
    Join Date
    11-09-2018
    Location
    Earth
    MS-Off Ver
    2013 / 2016
    Posts
    3

    Thumbs up Re: Conditional Multiple Criteria Lookup to Find Rows to Keep

    Dear 63FalconDude!

    That is utterly incredible how quickly you solved that! It worked perfectly and solved my requirements!

    Apparently, I was on the right track with my attempts... but I will need to study the details of how you did that so accurately and efficiently.

    A really HUGE THANKS to you #63falcondude

    All the Best!

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Multiple Criteria Lookup to Find Rows to Keep

    Happy to help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    I would be happy to explain the formula if you would like.

  5. #5
    Registered User
    Join Date
    11-09-2018
    Location
    Earth
    MS-Off Ver
    2013 / 2016
    Posts
    3

    Re: Conditional Multiple Criteria Lookup to Find Rows to Keep

    If you wouldn't mind, I would LOVE an explanation! I still have trouble wrapping my head around array formulas and how they work ... I'll mark this as resolved.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Multiple Criteria Lookup to Find Rows to Keep

    {=INDEX(UAID,MATCH(A2&MAX(IF((UserNr=A2)*(EndDate>=EDATE(TODAY(),-12)),Score)),UserNr&Score,0))}

    Let's work from the inside out.

    {=MAX(IF((UserNr=A2)*(EndDate>=EDATE(TODAY(),-12)),Score)} returns the maximum value in the Score range where the UserNr = A2 and the EndDate is greater than or equal to 12 months ago (given by the EDATE function).

    A2&MAX(IF()) combines the value in A2 with the result of the max if functions.

    The MATCH function looks for that combination in the UserNr&Score ranges and returns the row number of the first match.

    The INDEX function then returns the nth value from the UAID range where n is the number from the MATCH function.

+ 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. [SOLVED] using a lookup formula to find all rows that match same criteria
    By nicki_rae22 in forum Excel General
    Replies: 3
    Last Post: 12-09-2014, 10:38 AM
  2. Replies: 2
    Last Post: 05-10-2012, 10:38 AM
  3. [SOLVED] Conditional Lookup on Multiple Criteria
    By TBarker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 12:05 PM
  4. Conditional Lookup on Multiple Criteria
    By TBarker in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Conditional Lookup on Multiple Criteria
    By TBarker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Conditional Lookup on Multiple Criteria
    By TBarker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] Conditional Lookup on Multiple Criteria
    By TBarker in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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