max number based on multiple values
 

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.

max number based on multiple values
 

Hi,

Try this array formula:

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

HTH
Jean-Guy

"notso" wrote:

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.

max number based on multiple values
 

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(D2:D5,MATCH(MAX(IF((A2:A5="c")*(B2:B5=2),C2 :C5)),C2:C5,0))
again enter using CSE

Cheers!
Jean-Guy

"notso" wrote:

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.

max number based on multiple values
 

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?

"pinmaster" wrote:

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(D2:D5,MATCH(MAX(IF((A2:A5="c")*(B2:B5=2),C2 :C5)),C2:C5,0))
again enter using CSE

Cheers!
Jean-Guy

"notso" wrote:

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.

max number based on multiple values
 

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!!!!

max number based on multiple values
 

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(D2:D5,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





"notso" wrote:

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!!!!

max number based on multiple values
 

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.

max number based on multiple values
 

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

"notso" wrote:

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.

max number based on multiple values
 

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