Looking up the Latest Date (in time) Given 2 Other Criteria Match

[Cliff101]

Hello All,

This may have a fairly easy solution but I am an intermediate Excel user so I am not fully versed in some of the more detailed formulas.

I hate two table side by side on the same worksheet.

Table 1 has columns:

1. Item
2. Sales order #
3. Quantity
4. Expected Delivery Date

Table 2 has columns:

1. Item
2. Sales order #
3. Quantity
4. Expected ship date

Table 2 is when I am expecting to receive the said items and quantities and table 1 is when customer a is expecting me to deliver the same items and quantity.

For each row in table 1, I want to highlight it if the expected ship date in table 2 is later than the expected delivery date in table 1. But since I can have multiple entries of the same sales order in table 2, I need this conditional formating to take into account the following.

Highlight row in table 1 if:

1. Item
2. Sales order #

Then select the latest date in table 2 (date in time, not in sequence) and if the latest date in table 2 is later than the date in table 1, highlight the row in table 1 in red.

I would prefer to do this using formulas please. I really appreciate any feedback anyone has on this.