+ Reply to Thread
Results 1 to 3 of 3

Trying to pull data from cell based on criteria in another cell, while skipping blank cell

  1. #1
    Registered User
    Join Date
    12-01-2016
    Location
    Denver
    MS-Off Ver
    Mac 2011
    Posts
    10

    Trying to pull data from cell based on criteria in another cell, while skipping blank cell

    I am working on a sheet involving various placement data. I want to have individual sheets for each class which includes only the students who place into that class and ignores students who do not place into that class; however, my Excel knowledge does not go far beyond IF or INDEX and MATCH. I cannot figure out how to use a formula to skip cells/rows that do not meet the criteria.

    Sample sheet is attached.

    The Overview sheet houses all placement data. On ENG 121 I want to only pull names that have "Yes" under ENG 121 on the overview sheet. The closest I know how to get is an IF function, but that returns 0 for kids w/o Yes. I need it to skip them.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Trying to pull data from cell based on criteria in another cell, while skipping blank

    In H2, I2 and J2

    =IFERROR(INDEX(Overview!$E:$E,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

    =IFERROR(INDEX(Overview!$A:$A,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

    =IFERROR(INDEX(Overview!$B:$B,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

    Enter all above with Ctrl+Shift+Enter


    Copy down

  3. #3
    Registered User
    Join Date
    12-01-2016
    Location
    Denver
    MS-Off Ver
    Mac 2011
    Posts
    10

    Re: Trying to pull data from cell based on criteria in another cell, while skipping blank

    Quote Originally Posted by JohnTopley View Post
    In H2, I2 and J2

    =IFERROR(INDEX(Overview!$E:$E,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

    =IFERROR(INDEX(Overview!$A:$A,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

    =IFERROR(INDEX(Overview!$B:$B,SMALL(IF(INDEX(Overview!$I$2:$O$11,,MATCH("ENG 121",Overview!$I$1:$O$1,0))="Yes",ROW(Overview!$A$2:$A$11),""),ROWS($A$2:A2))),"")

    Enter all above with Ctrl+Shift+Enter


    Copy down
    This solved it. Thank you so much. Is there a resource where I can see the logic of this formula explained? I understand the INDEX MATCH logic and the basic logic of formulas, but I cannot figure this one out.

    Thanks again

+ 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] If cell blank don't allow data validation via a pull down list in a next cell
    By cwby44 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2014, 11:30 AM
  2. Macro to pull data from one cell based on certain criteria from 3 other cells
    By The Fly in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2014, 01:46 PM
  3. Skipping a blank cell and checking next cell in a column
    By trevor2524 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-17-2013, 11:11 PM
  4. Replies: 4
    Last Post: 05-29-2013, 04:18 AM
  5. Formula to pull rows of data based on a column cell value (Criteria)
    By PaulLor89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 11:26 AM
  6. Replies: 0
    Last Post: 03-05-2013, 09:22 AM
  7. [SOLVED] Pull data from multiple cells into one cell based on criteria?
    By chaddug in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-23-2012, 09:54 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