IF statement & corresponding max value

[JonElCanche]

I am close on this, but can't quite get it figured out. Thanks for the help!

Below is the data that I am working with. In a new cell I want to create a formula that will match the License # in column A with the correct Maintenance in column B and give me the latest date for that particular License # and Maintenance. The formula that I have tried so far is:

=if(and(columnA="KP3157", columnB="Adjust Valve Clearance), max(columnC), "N/A")

This formula almost works but it just gives the latest date entered in column C, 1/22/2012, and the answer should be 10/15/2011. Any ideas?


<table border="0" cellpadding="0" cellspacing="0" width="603"><col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:11629;width:239pt" width="318"> <col style="mso-width-source:userset;mso-width-alt:6985;width:143pt" width="191"> <tbody><tr><td style="vertical-align: top;"> A
</td><td style="vertical-align: top;"> B
</td><td style="vertical-align: top;"> C
</td></tr><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:71pt" height="20" width="94">License #</td> <td class="xl66" style="width:239pt" width="318">Maintenance
</td> <td class="xl66" style="width:143pt" width="191">Date</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3157</td> <td class="xl64">Adjust Valve Clearance</td> <td class="xl65">8/25/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3164</td> <td class="xl64">Air Filter</td> <td class="xl65">9/22/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3175</td> <td class="xl64">Alignment</td> <td class="xl65">9/23/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3157</td> <td class="xl64">Springs</td> <td class="xl65">10/10/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3168</td> <td class="xl64">Oxygen Sensor</td> <td class="xl65">10/30/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3157</td> <td class="xl64">Adjust Valve Clearance</td> <td class="xl65">10/15/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3164</td> <td class="xl64">Air Filter</td> <td class="xl65">11/22/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3175</td> <td class="xl64">Alignment</td> <td class="xl65">11/23/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3157</td> <td class="xl64">Springs</td> <td class="xl65">12/10/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3168</td> <td class="xl64">Oxygen Sensor</td> <td class="xl65">12/30/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">KP3157</td> <td class="xl64">Cap, Rotor, & Wire</td> <td class="xl65">1/22/2012</td> </tr> </tbody></table>

 

[T. Valko]

I am close on this, but can't quite get it figured out. Thanks for the help!

Below is the data that I am working with. In a new cell I want to create a formula that will match the License # in column A with the correct Maintenance in column B and give me the latest date for that particular License # and Maintenance. The formula that I have tried so far is:

=if(and(columnA="KP3157", columnB="Adjust Valve Clearance), max(columnC), "N/A")

This formula almost works but it just gives the latest date entered in column C, 1/22/2012, and the answer should be 10/15/2011. Any ideas?


<TABLE cellSpacing=0 cellPadding=0 width=603 border=0><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 239pt; mso-width-source: userset; mso-width-alt: 11629" width=318><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><TBODY><TR><TD style="VERTICAL-ALIGN: top">A


</TD><TD style="VERTICAL-ALIGN: top">B


</TD><TD style="VERTICAL-ALIGN: top">C


</TD></TR><TR><TD style="VERTICAL-ALIGN: top">


</TD><TD style="VERTICAL-ALIGN: top">


</TD><TD style="VERTICAL-ALIGN: top">


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="WIDTH: 71pt; HEIGHT: 15pt" width=94 height=20>License #</TD><TD class=xl66 style="WIDTH: 239pt" width=318>Maintenance


</TD><TD class=xl66 style="WIDTH: 143pt" width=191>Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3157</TD><TD class=xl64>Adjust Valve Clearance</TD><TD class=xl65>8/25/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3164</TD><TD class=xl64>Air Filter</TD><TD class=xl65>9/22/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3175</TD><TD class=xl64>Alignment</TD><TD class=xl65>9/23/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3157</TD><TD class=xl64>Springs</TD><TD class=xl65>10/10/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3168</TD><TD class=xl64>Oxygen Sensor</TD><TD class=xl65>10/30/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3157</TD><TD class=xl64>Adjust Valve Clearance</TD><TD class=xl65>10/15/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3164</TD><TD class=xl64>Air Filter</TD><TD class=xl65>11/22/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3175</TD><TD class=xl64>Alignment</TD><TD class=xl65>11/23/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3157</TD><TD class=xl64>Springs</TD><TD class=xl65>12/10/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3168</TD><TD class=xl64>Oxygen Sensor</TD><TD class=xl65>12/30/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>KP3157</TD><TD class=xl64>Cap, Rotor, & Wire</TD><TD class=xl65>1/22/2012</TD></TR></TBODY></TABLE>

Try this array formula**:

=MAX(IF(A2:A100="KP3157",IF(B2:B100="Adjust Valve Clearance",C2:C100)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

 

[JonElCanche]

Wow, worked like a charm! You rock Valko!!! Thank you

 

[T. Valko]

Wow, worked like a charm! You rock Valko!!! Thank you

You're welcome. Thanks for the feedback!