Return value per record (where >1 row per record) based on criteria for multiple columns

Please attach a sample workbook with expected output for better understanding

Hello,

If you have already sorted, Excel should sort it with priority, like from student 1 (First Name, Last Name), then it will sort Score, Date, then Error.
On your Sample, all of your choice are the top row of that student's results, so you can make an easy Array formula like this
=LARGE(IF($A$3:$A$11=$A15,$B$3:$B$11),1)
When you enter it, hold Ctrl-Shift then hit Enter, because it's an Array formula

Hope this help.

I have a question, it has been bugging me the whole time I'm trying to do this.
If the student no.1 for example, scored 93 with 4 errors and 95 with 6 errors, both on 4/29/2013, then basing on your priority list, the score of that student will be 93 instead of 95, because both are most recent, and the 93 one has smallest number of errors?

If nothing changed, and if student no.1 still get the score 93 in the question above, this might be the formula you are looking for

Please Login or Register  to view this content.

If you want to use it, hold Ctrl-Shift then hit Enter after you edit / put it in a cell, because it is an array formula.
And here is the sample file - Question2.xlsx
it took me literally 1 hour to figure this out, I feel like learning how to do math all over again ...
If you want to discuss about how I make this up, I'll gladly share it over.

Hope this help.

P/s: You might find some cell with different value than your original file, it's because I changed them intentionally to test out every possibility.

Hello,

This look-complicated formula is actually a series of LARGE and SMALL function. I combined 3 function together, and you can say they search within other's results, like how you eliminating scores using your priority.
The core of the function, aka the first one to calculate before anything else is your rule number 3

Please Login or Register  to view this content.

What this does is identify the most recent date accordingly to the number / name of student in Cell A15
And I notice something, in the date column, even this most recent date is not absolute - it might not be the most recent date for others, or it is, leading to duplicated results. However, there is something that will make it unique - I add the number / name of student into it, making it a combined string, and it WILL become unique. Think of "April 29", there are lots of rows with that, but "April 29 OF Student 1", only a handful, isn't it?
So, my idea here is to combine the Array of result (there might be more than 1 test with most recent date) and the unique id of student (name, number, etc) and create a new Array to look for, hence

Please Login or Register  to view this content.

If you break it down, it actually means this
=SMALL(IF("List of ID"&"List of Date"="ID student no.1"&"Most recent Test Date for no.1",1),"Results if match"),1)
This serves as the function to find the smallest error among all most recent date of a student.
And again, with the very same idea, there can be more than one result, right? Thus, I added the final touch for it, and here we go

Please Login or Register  to view this content.

It actually means this
=LARGE(IF("List of ID"&"List of Errors"="Student no.1"&"Smallest Errors in Most Recent date"),"Results"),1)
This function is the final to calculate, and basing on all of the criteria above, it will find the largest score that fits in.
For your question, I think you can do it, but I suggest making an independent formula to calculate it though, because dealing with Array isn't always ideal.

Hope this help.

Eventually you will want to use Array formula because you are finding a value base on a list of value in random order.

However, there are things that can make your table run faster:
- Give the formula Specific Cell Reference, for example A3:A11 is MUCH faster than A:A. This can be done through INDIRECT pointing to a MATCH to find the last row with active cell.
- Split the formula into smaller one with helper columns.

If you can provide a sample with about 1000 dummy data (You can make use of RANDBETWEEN here and there), and your condition of how many helper column maximum, etc I will see what I can do. Other than that, you might want to look out for a macro.