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.

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.

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.

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.

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

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

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.

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.

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