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

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

2. ## 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. ## Re: Trying to pull data from cell based on criteria in another cell, while skipping blank

Originally Posted by JohnTopley
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

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