Displaying a date in a column of only a row that is the most critical of two other columns

I have a sheet with groups of three levels of maintenance for each piece of equipment. I have almost got an array formula working where it only displays the date of the most overdue. What I need is the most overdue and the highest level of maintenance. Column F should only show for each group of three in Column A the combination of the most due in Column D and the highest level in Column C. It would be perfect if I did not need to carefully sort A and D for the formula to work as my solution does.

Any assistance would be greatly appreciated. All hair almost now pulled out in frustration.

Originally Posted by Miraun

Hey Nubsikins;

So, a question regarding the anticipated result.

I'm seeing the first vehicle, 70494, has ALL maintenance overdue. However, Level 1 is the most overdue of the three. However, your requirements seem to be asking for the most severe maintenance that's due. So... what would be the anticipated result of the formulas for vehicle 70494... Level 3 maintenance required, all three overdue?

Also, taking a look at the sample of data that you gave us, it also looks like Level 1 will always be due first, and that the dates will correspond to the next tier of maintenance that's done... Therefore, if two Maintenances are overdue, it will be Level 1 and Level 2 maintenance, and never be only level 2 and level 3... If this is the case, a simple countif could work on the overdue column to highlight the necessary level of maintenance. I can help you out more if this is in fact true.

Thanks for your reply Miraun, the highest level of maintenance is most important then if the maintenance is overdue. A date should appear against the L3 only for the vehicle you have highlighted.

The sequence of the maintenance is more complicated than is appears. It goes L1-L1-L1-L2-L1-L1-L1-L3 so the second solution will not work. This is the worst subset of a very large dataset.

Thanks for your assistance.