Using COUNTIF within VLOOKUP for course completion

user10486505 Source

Students are required to complete 4 courses. My query pulls all students by admit term and only includes the courses they've completed. How do I determine which student is missing one or more of the 4 courses?

Need to pull which students do not have all four courses listed.

1010293 Smith, Joe  ENGL    10803
1010293 Smith, Joe  MATH    10283
1039283 Todd, Don   ENGL    10803
1039283 Todd, Don   MATH    10283
1039283 Todd, Don   ECON    10233
1767289 Park, Kim   ENGL    10803
1767289 Park, Kim   MATH    10283
1767289 Park, Kim   ECON    10223
1767289 Park, Kim   ECON    10233

V Look Up   
ID# 1010293
Name    Smith, Joe
ENGL 10803  complete
ECON 10223  missing
ECON 10233  missing
MATH 10283  complete


answered 6 days ago Christopher #1

There are lots of ways to do what you want. What seems most straightforward to me, is to create a matrix on a second worksheet. You can use the following steps.

  1. Open a new worksheet. The first row could have the possible headers:

    Student No | Student Name | Engl 10803 | Math 10283 | Econ 10223 | Econ 10233 
  2. Copy the list of students' ids in Column A. Highlight the pasted ids and clear duplicate numbers by using Excel's remove duplicate function. Data>Data Tools>Remove Duplicates

  3. Use VLOOKUP if you want to pull in student names. something like =VLOOKUP(A2,Sheet1!$A$2:$B$10,2,FALSE) in B2 of your new sheet.

  4. Use an IF array function to determine if those students have taken those classes. Based on your test data, the following function appears to work.

    =SUM(IF($A2=Sheet1!$A$2:$A$10,IF(Sheet1!$C$2:$C$10&" "&Sheet1!$D$2:$D$10=Sheet2!C$1,1,0),0))
  5. This is an array function, so copy and paste it into C2, then submit the function by using ctrl+shift+enter. Then you can copy and paste that cells into the range of students C2:F4 on your test data.

This function works because Sheet1!$C$2:$C$10&" "&Sheet1!$D$2:$D$10 returns the subject and course number in the same format as the column headers of the second pages.

  1. For easy sorting, you can make apply a filter or table format Home>Styles>Format as Table or Data>Sort and Filter>Filter

comments powered by Disqus