+ Reply to Thread
Results 1 to 6 of 6

Finding all rows in a range that match two criteria

  1. #1
    Registered User
    Join Date
    08-20-2016
    Location
    Tennessee
    MS-Off Ver
    Mac 2011
    Posts
    4

    Question Finding all rows in a range that match two criteria

    I am currently using the following formula to find all the row numbers in a one-dimensional range called "StudentList" that match a value stored in C3. I'm using this data to retrieve all of the matching records.

    =if(iserror(ArrayFormula(SMALL(IF(StudentList=$C$3,ROW(StudentList)),ROW(1:1)))),"",ArrayFormula(SMALL(IF(StudentList=$C$3,ROW(StudentList)),ROW(1:1))))

    I now have need to only return those rows where the corresponding row in a related range called "InteractionList" also match the value stored in C4.

    I have tried placing an OR statement around "StudentList=$C$3" and include something like "InteractionList=$C$4", but it doesn't work. I don't think the formula is tracing through the two ranges simultaneously.

    Any suggestions?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Finding all rows in a range that match two criteria

    Hi, welcome to the forum

    1. That is an ARRAY formula and needs to be entered using CTRL SHIFT ENTER, not just ENTER
    2. I think that, instead of just ArrayFormula, you should have something like INDEX(your-range,
    3. You can shorten that whole thing by using IFERROR...
    =iferror(ArrayFormula(SMALL(IF(StudentList=$C$3,ROW(StudentList)),ROW(1:1)))),"")
    4. the usual way of using the last argument is rowS($A$1:A1)

    It would probably also help if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-20-2016
    Location
    Tennessee
    MS-Off Ver
    Mac 2011
    Posts
    4

    Post Re: Finding all rows in a range that match two criteria

    Thanks for the tip on the IFERROR...that really did shorten the formula.
    The sheet in question is "Individual Student Record." I am currently using Col G to find all the records in my range FormattedData (from sheet Formatted Data) that matches the name in C3. I would like to change this formula to also check to see if C4 equals the corresponding value in the range "InteractionList" ('Parent' or 'Student') or if C4="All."

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Finding all rows in a range that match two criteria

    on Indiv Stud sheet, B:E, you dont need to enter those formulas as ARRAY (CTRL SHIFT ENTER), just enter them normally...
    =IF($G7="","",INDEX(FormattedData,$G7-1,2))

    Will you always only have 1 match for what you are searching?

  5. #5
    Registered User
    Join Date
    08-20-2016
    Location
    Tennessee
    MS-Off Ver
    Mac 2011
    Posts
    4

    Re: Finding all rows in a range that match two criteria

    No. The data on Formatted Data will represent every interaction with a student or parent. There will be multiple interactions per student and parent. I want the list on Individual Student Report to represent all of these interactions. This array formula works well when I just try to match the student name, but I can't seem to get it to work when I try to also match the type of interaction...parent, student, or all.

    This is in essence what I want to do:

    =IFERROR(SMALL(IF(AND(StudentList=$C$3,OR($C$4="ALL",InteractionList=$C$4)),ROW(StudentList)),ROW(1:1)),"")

    But, this does not work.

  6. #6
    Registered User
    Join Date
    08-20-2016
    Location
    Tennessee
    MS-Off Ver
    Mac 2011
    Posts
    4

    Re: Finding all rows in a range that match two criteria

    I believe I have it figured out:

    =if($C$4="ALL",iferror(SMALL(IF(StudentList=$C$3,ROW(StudentList)),ROW(1:1)),""),iferror(SMALL(IF((StudentList=$C$3)*(InteractionList=$C$4),ROW(StudentList)),ROW(1:1)),""))

    It appears to work. I'll know more tomorrow after I have had some sleep.

+ 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] Finding match based on 2 criteria
    By NU2vba in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2015, 02:17 PM
  2. Finding Min/Max of B:B to match criteria in A:A
    By Pilgrimpete in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2015, 06:55 AM
  3. Finding a closest match with more than one criteria
    By Jayana in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-15-2011, 06:45 PM
  4. Replies: 5
    Last Post: 09-22-2009, 06:11 PM
  5. Range match finding
    By calsexceladel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2008, 08:42 PM
  6. Finding criteria and removing matching rows (Range issue?)
    By Ronny Hamida in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2006, 11:25 AM
  7. Tough One: Finding Match Based on Criteria, Returning Value
    By christopher.sul in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-02-2005, 01:42 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