Multi Criteria Lookup

I am trying to find a script or formula to perform a multi-criteria search with the following three criteria:
scrubshot.JPG

1. Invoice number
2. Transation number contains Y4 or YK (indicated in transation number and also leftmost column)
3. For multiple results, select the status associated with the latest date

So, in other words, if we search for Y4 or YK associated with a certain invoice number, it may return multiple results. In the case that it does, we need the script/formula to return the status associated with the result that occurred latest. In the case of the attached, searching for a certain transaction number can result in multiple instances of the same invoice but with different statuses. We need to pull the status associated with the latest date.Scrub Data.xls

Please let me know if I can clarify this further.