Selected Answer
Because it's easier to read formulas with named ranges and because it's easier to specify dynamic ranges when they are named I created two named ranges in your worksheet as follows.
- B5:B8 As "Sites"
- D5:D8 As "Submitted"
In the formulas below you may replace the names with the hard range addresses if you prefer.
Now consider this IF statement. It says, if the number of occurrences of "Orange" in Sites is equal to the number of dates associated with "Orange" in Submitted, then the condition is True, otherwise it's False.
=IF(COUNTIF(Sites,K5)=COUNTIFS(Sites,K5,Submitted,"<>"),TRUE,FALSE)
This solves a part of your problem. If a date is missing you want to return "". Otherwise you want the most recent of the dates. This is the formula that extracts the latest of the dates associated with "Orange".
=MAX(IF(Sites=K5,Submitted))
This formula will return what I call a "True date", meaning, it will return a number. To display the number as a date you need to format the cell. The date will then be displayed in the format you have set for the cell.
Now all that remains to be done is to replace the two results in the IF statement first above introduced. Paste the merged formula to L5 and copy down as required by column K. (The formula will throw an error if the named ranges haven't been set up first.)
=IF(COUNTIF(Sites,K5)=COUNTIFS(Sites,K5,Submitted,"<>"),MAX(IF(Sites=K5,Submitted)),"")
In your Excel 365 this formula will work just as it is but if someone has an older Excel version it must be entered as an array formula with CTL + SHIFT + ENTER.
