Finding the latest data multiple criteria are met

[alex_a]

Hi everyone,

I have a list of students in column A (student id number in A1 thru A70), in column B I have test 1 scores, in column C, I have the dates scores were registered. I have test 2 scores in column D, I have the dates scores were registered, so on and so forth. I need to know when was the latest date for each student to have the passing score.

Student Test 1 Score Test 1 Date Test 2 Score Test2 Date Test 3 Score Test 3 Date Test 4 Score Test 4 Date
1234
4569
8563
4569


I need to find the latest date when let's say student 1234 had the test 1 score of 40 and over, test 2 score of 50 or over, test 3 score of 70 and over and test 4 score of 80 and over. Student 1234 might have had several scores for each test at different dates. When all of his passing scores are met, I need to find the date. Thanks for your assistance. Since all scores are in different sheets, I need a formula saying if (student id = 1234), then find the latest date when criteria for each test is met. Thanks.

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic