Extract Student Summary Data from Another Sheet Based on Various Status in Microsoft Excel

In this article, you will learn how to get summary status of student from Source sheet to Summary sheet based on multiple checks.


I have two sheets i.e. Source & Summary. In Source sheet, we have Name of Student, ID, Subject, Completion Date & Completion Status.

  • I want a formula to check for each student & subject in Source sheet & extract completion date if not completed then pull the status.

Following is the snapshot of Source sheet:




Following is the snapshot of Summary sheet:




  • In cell D2, the formula is { =IFERROR(IF(INDEX(Source!$D$2:$D$12,MATCH(1,(Source!$A$2:$A$12=$B2)*(Source!$C$2:$C$12=D$1),0))=0,INDEX(Source!$E$2:$E$12,MATCH(1,(Source!$A$2:$A$12=$B2)*(Source!$C$2:$C$12=D$1),0)),INDEX(Source!$D$2:$D$12,MATCH(1,(Source!$A$2:$A$12=$B2)*(Source!$C$2:$C$12=D$1),0))),"Not Started")}

Note: This is an array formula; you are required to use CTRL + SHIFT + ENTER keys together

  • Drag down & across the formula in range D2:F6; we will get the status of all three subjects:




  • To update the combined status of all three subjects for each student marks In column G, we will use formula as =IF(AND(ISNUMBER(D25),ISNUMBER(E25),ISNUMBER(F25)),"Completed","Incompleted")




In this way, we can extract student summary data based on data in anther sheet.


Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.