max number based on multiple values

[Guest]

Name Course Complete Date Completion Status
A 1 6/21/2006 Complete
C 2 10/30/2006 Incomplete
C 3 03/30/2006 Complete
C 2 03/30/2006 Complete

Above is my table. I need a formula that will take the Name & the Course #
as input and return the completion status of the most recent date of a course
taken.

Using the above table; Student "C" & Course "2" should return "Incomplete"
because October is the most recent date.

 

[Guest]

Hi,

Try this array formula:

=INDEX(D25,MATCH(MAX(IF((A2:A5=D8)*(B2:B5=F2),C2:C5)),C2:C5,0))
enter using Ctrl+Shift+Enter

HTH
Jean-Guy

 

[Guest]

Hi again

Forgot to mention that in my formula D8 contained the letter C and F2 the
number 2. Should have given you this instead for a better understanding.


=INDEX(D25,MATCH(MAX(IF((A2:A5="c")*(B2:B5=2),C2:C5)),C2:C5,0))
again enter using CSE

Cheers!
Jean-Guy

 

[Guest]

Thank you! I don't quite understand how it works, but it does.

Another question, my table varies in length, how do I automatically change
the length based on another variable.

Example the table I sent is 4 rows; the next table might be 8 rows; the next
one might be 2. Can I set a variable to automatically change the length?

 

[Guest]

One more question; how do I return the value "Student Not Found" if the
student is not in the table or the value "Course Not Found" if the course is
not in the table?

Thank you!!!!

 

[Guest]

Hi,

One way:

=IF(COUNTIF($A$2:$A$5,"C")=0,"Student not
found",IF(COUNTIF($B$2:$B$5,2)=0,"Course not
found",INDEX(D25,MATCH(MAX(IF((A2:A5="c")*(B2:B5=2),C2:C5)),C2:C5,0))))
enter with CSE

As for the variable, you could by using the INDIRECT function but that would
lengten the formula considerably. Let's take a sum formula as an example:

=SUM(INDIRECT("A1:A"&B1))
where B1 is the cell with the variable, you can use the same principle to
create your formula.

HTH
Jean-Guy

 

[Guest]

I must be doing something wrong. If the completion dates are all the same,
then the value returned is always the first completion status. No matter what
the student name is.

 

[Guest]

Hi,

No you're not doing anything wrong, that is the nature of the formula when
you have a match. I'm not an expert so I do not know how to get around that.
It could be tricky but I'm sure the MVP's in here have a solution for you. I
suggest posting again and also post your current formula to give them a
better look at your setup. Sorry to have wasted your time.

Good Luck!
Jean-Guy

 

[Guest]

No problem. Your answer was helpful and I figured out how to adjust the
formula.