Formula to automatically search multiple criteria and extract multiple columns in row

  • Hi, I started earlier this year in teaching myself Excel (using MSO 2007) so that I could use it to create databases containing my student information and marks. I enjoy solving the puzzles myself. But enjoy even more from learning from others how they solved it. But I am still a complete newbie to this programme and have hit a bump in the road. I have gone through all the other forum questions but still feel that somehow the questions are missing mine by a nanometer.

    I am looking for the solution to be a formula, have absolutely no idea how to work pivot tables and VBA codes. I guess that would be my 2017 task to work on and learn

    My Registrar has asked me to create a little system for her to capture students marks. I had created on top of that task another sheet that would allow her to enter a Student's number and course code to retrieve their assessments.

    The Context:

    The Database Contains the following:

    Sheet1 = Index (Index to all the sheets - not updated for some time, will do so later)
    Sheet2 = Student SEARCH (Registrar can search for student details as well as individual completion of assessment)
    Sheet3 = Student Database - 2016 (Contains all the students' information)
    Sheet4 = Course details Database - 2016 (Contains all the courses' information)
    Sheet5 = Assessment Types - 2016 (Contains the different types of assessments)
    Sheet6 = Registrar Assess. Control Form (This is where the Registrar will need to capture her information for the student)
    Sheet7 = Living The Faith II - S2 (**PROBLEM AREA**)

    **The Problem**

    In Sheet7 named 'Living The Faith II - S2' I would like to do the following:

    The sheet will deal solely with its course code 'ST202'. I would the code to search through Sheet6 named 'Registrar Assess. Control Form' for all the entries that contain 'ST202' and then to match it with all of its assessments for a single student number.

    Therefore it would be like this:

    If 'Code' = 'ST202' and is TRUE,

    Then does 'Student No.' + 'ST202' + (if (Assessment Type = Practical1) = TRUE, AND if(Assessment Type = Formative) = TRUE

    Then automatically display:
    Student Number, Surname, Name, Assessment Mark (Practical1), Assessment Mark (Formative)

    and then do the same thing for the next line until all that is TRUE has been copied to the new Sheet. This sheet will then allow my Registrar to see at a glance who is ready for exams. The Summatives will be excluded from the calculations. But at times depending on the course code there is more than one Practical assessment (i.e. Practical1, Practical2,Practical3). But that would only be necessarily worried about in their own specific Sheet number. There will be about 31 Sheets for each course, so codes will differ accordingly to their course code.

    As by the title of my workbook attached below, you can see that I am just 'fooling around' until I can get the code working.

    You will see quite a number of other formulae written there as I have tried each one. I KNOW that the solution should be using commands such as IF, INDEX, MATCH and SMALL, with a few others. But I must be missing something.

    I hope that the outline of my problem is straightforward. I apologise if I 'rambled' on too much...or not enough ;)

  • Re: Formula to automatically search multiple criteria and extract multiple columns in

    Hi and welcome to the forum,

    Take a look at the sheet and see if this is what your after.
    Hope this helps you on your investigating journey into the wonderous world of excel.